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

[rob@xxxxxxxxx]

Depending on what version of OS/400 you are on.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Walden H. Leverich III" <WaldenL@xxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/28/2003 01:58 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'Midrange Systems Technical Discussion'" 
<midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        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.
_______________________________________________
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.