Re: AW: SQO Join Update Question

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