RE: While we're on the subject, a more complicated SQL question.

Rick,

Ideally you'd do it with a join, but last I knew you can't have a join on an
update (but I haven't checked in a while) However, you can do it with
subselects, I haven't checked this, but if memory serves you're very close.
Try:

Update File1
        set code = (select newcode
                        from file2
                        where vend1 = vend2)

There is no need to use file1 in the subquery. The values from the outer
query are available in the inner query. Basically, the inner query runs once
for each row in the outer query.

The only thing I'd want to double check is what happens if there is no hit
in the inner query, does that set 'code' to null or skip the row completly? 

You may have to do a COALESCE to account for the null and I don't know if
COALESCE can take a subquery as a parm. If it does, great, if not you need
to add a where clause to the outer query selecting rows WHERE vend1 IN
(SELECT distinct vend2 from file2)

-Walden

------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 

-----Original Message-----
From: Richard B Baird [mailto:rbaird@xxxxxxxxxxxxxxxxxxxxx] 
Sent: Friday, March 28, 2003 12:53 PM
To: Midrange Systems Technical Discussion
Subject: While we're on the subject, a more complicated SQL question.



all this SQL talk, and I come up with a doozie.

can you do a UPDATE to one file based on the contents of another file?
Something like this:

file1  (has all vendors)
 vend1
 code
 other
 stuff

file2  (only has selected vendors, not all)
 vend2
 newcode

now, I'm just guessing, to give you the idea of what I'm trying to do:

  update file1
     set code = (select newcode
                   from file1,
                        file2
                  where vend1 = vend2)

am I on the right track, or am I just p***ing in the wind?

thanks,

rick

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.