Re: Newbie SQL problem with UPDATE - SQL0811 - repost

Bob,

When you run the sub-select

SELECT b.NUMBER
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)
order by b.number

Do you get ANY number more than once? Not only does the result not allow
nulls, but you can only have one matching record returned by the
sub-select.

How about;

SELECT distinct b.NUMBER
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)

as your sub-select?

HTH

Jim

On Fri, Apr 10, 2009 at 1:15 PM, Bob Schwartz <rschwartz@xxxxxxxxxxxxxxx>wrote:

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))