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,
It's telling you whatt he problem is, your select:
SELECT b.number
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)

Returns more than one row.

It doesn't matter if all the rows returned have the same value for
number or not, the statement will not work if the select returns more
than one row.

So you need to change it such that it only returns one row.
Assuming there's only one value but that it appears in multiple rows,
this works.
SELECT DISTINCT b.number
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)

Otherwise, you need some way to pick one of the values returned,
min/max is one possiblity:
SELECT max(b.number)
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)

Lastly,
You shouldn't need both the COALESCE and the WHERE EXISTS. Either one
of them will prevent the error you get when there's no match in
LRICEXTWF for a record in LRACCTWF and the file in LRACCTWF is not
nullable.

HTH,
Charles