RE: Newbie SQL problem with UPDATE - SQL0811 - repost

If you really need to update all of the rows, then DON'T use the WHERE
EXISTS clause. But do you really want to update the row in LRACCTWF if
there is no matching row in LRICEXTWF? If so, then COALESCE is appropriate,
but what is the alternate value you want to update it with when there is no
join?

Regardless of the COALESCE vs. WHERE EXISTS decision, you will still get the
same error since you haven't corrected the original issue one-to-many join
for some of your rows.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Newbie SQL problem with UPDATE - SQL0811 - repost

Charles and group,

I'm trying to update the all the records in LRACCTWF.
I need to update the school number in LRACCTWF with the school number from
LTICEXTWF,
by matching on student PIN - LRPIN in LRACCTWF and STUDE00001 in LRICEXTWF.
Thanks folks, Bob

Here's my latest try:
UPDATE LRACCTWF a
SET a.NUMBER = (SELECT b.NUMBER
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
WHERE EXISTS
(SELECT *
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))




Bob