RE: Problem with SQL updating a field from a field inanotherfile-Resolved, thank you! -- MIDRANGE-L

Thank you for taking the time to provide the explanation Charles. I thought 
that the 1 had a special meaning (something like the A, B, C... qualifiers for 
the file names) but I understand it's basically a work-around and can be any 
value.
 
 
Valerio Vincenti
IT Business Analyst
County of Spotsylvania, I.S. Department
Spotsylvania, VA 22553
Phone (540) 507-7507
Fax (540) 582-9841
e-mail: vvincenti@xxxxxxxxxxxxxxxxxx

CWilt@xxxxxxxxxxxx 10/20/2006 11:01 AM >>>

Valerio,

Elvis' statement is just another way to implement my "option 2 - don't
update #5 at all"

WHERE EXISTS <subselect>

Is somewhat difficult to wrap your head around, but once you understand
it's not so bad.

First off, the fields selected in the <subselect> DO NOT MATTER as
nothing is done with the selected fields.  You are only interested if a
row exists that COULD be return by the <subselect>.  But the row is
never really returned.
  Lots of times, you'll see written:
   WHERE EXISTS (select *
                 from myfile
                 where fld1 = a
                 )

Historically, some DB's performed better when you used a constant value,
ie. 1, instead of the *.  The iSeries doesn't care.

Elvis' statement:
update PROJECT A set A.EMPNAM = (select B.NAME 
                                 from EMPMASTER B 
                                 where A.EMPCDE = B.CODE)
WHERE EXISTS (select 1
              from EMPMASTER B 
              where A.EMPCDE = B.CODE)

In English would be:
update PROJECT A set A.EMPNAM = (select B.NAME 
                                 from EMPMASTER B 
                                 where A.EMPCDE = B.CODE)
WHERE (there exists a row in EMPMASTER whose CODE field contains the
same value as the EMPCDE field of the row in PROJECT that I'm currently
looking at to determine if I should update it or not.)


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx 
[mailto:midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx] 
On Behalf Of Valerio Vincenti
Sent: Friday, October 20, 2006 10:28 AM
To: midrange-l@xxxxxxxxxxxx 
Subject: RE: Problem with SQL updating a field from a field 
in anotherfile -Resolved, thank you!


I wish to thank those who responded to my original  post and, 
as always happens on this list, provided valuable input to 
the solution of my problem. Many of the suggested methods 
work fine and now I'm left with deciding which one should I 
use (life's hard).
 
I'm clearly not an SQL wizard, but the syntaxes that make 
more sense to my shallow mind are the two suggested by Charles:
Assign some other value instead of NULL to #5

1) update PROJECT A                              
set A.EMPNAM = coalesce((select B.NAME        
                          from EMPMASTER B       
                          where A.EMPCDE = B.CODE
                         )                      
                 , A.EMPNAM )                 
2) don't  update #5 at all
update PROJECT A 
set A.EMPNAM = (select B.NAME 
                from EMPMASTER B 
                where A.EMPCDE = B.CODE
               )
Where A.EMPCDE in (Select C.CODE 
                    FROM EMPMASTER C
               ) 
(I'll probably end up using option 2 simply because is closer 
to my way of thinking as an RPG programmer).
 
The method suggested by ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx 
also works fine:
 
update PROJECT A set A.EMPNAM = (select B.NAME 
                                 from EMPMASTER B 
                                 where A.EMPCDE = B.CODE)
WHERE EXISTS (select 1
              from EMPMASTER B 
              where A.EMPCDE = B.CODE)
although I have some difficulties understanding that "select 1" 
 
As far as the indications from Rob, I haven't really tried 
because they use inner joins and look to me more complex than 
the others.
 
Many thanks again. 

 
 
Valerio Vincenti
IT Business Analyst
County of Spotsylvania, I.S. Department
Spotsylvania, VA 22553
Phone (540) 507-7507
Fax (540) 582-9841
e-mail: vvincenti@xxxxxxxxxxxxxxxxxx 
-- 
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.