Re: AW: SQO Join Update Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

Elvis,
What I want to do is update all rows in vsadetl with the new data from vsaxref.
I am changing the item & location in vasdetl using the cross reference in vsaxref.
Thanks,
 
Jeff Young 
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3 


  
 





----- Original Message ----
From: Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, August 1, 2007 4:08:18 PM
Subject: RE: AW: SQO Join Update Question


So out of those many rows in vsadetl, which one do you want to use to update
a single row in vsaxref?
I don't know nor does DB2.  You have to tell it.
There are two ways you can do this.  You can make your key more selective
(i.e. add more fld1 = fld2 criteria, until you get only one row from
vsadetl).  This is the recommended method.

Another way implies you don't care which row from vsadetl is used, any will
do for this particular update, so you could cheat:

update foodwork/vsadetl 
    set (sbitem, sbloc) = (select min(xrto), min(xrtoloc)
                              from foodwork/vsaxref 
                              where sbcmp  = xrcmp  and 
                                    sbitem = xrfrom and 
                                    sbloc  = xrtoloc
                              group by xrto,xrtoloc) 
where exists (select 1 
                 from foodwork/vsaxref 
                 where sbcmp  = xrcmp  and 
                       sbitem = xrfrom and 
                       sbloc  = xrtoloc)                           

Not sure about the syntax, so please test it.
HTH.

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: Re: AW: SQO Join Update Question

Hi Birgitta,
I tried your solution, but am getting the following message:
Result of SELECT more than one row

There are multiple records in the vsadetl for each record in vsaxref.

TIA,

Jeff Young