RE: Newbie SQL problem with UPDATE - SQL0811 - repost

[Bob Schwartz <rschwartz@xxxxxxxxxxxxxxx>]

FWIW I just tried this ...

         ************************** Beginning of Data *********************************
 0001.00 UPDATE LRACCTWF a
 0002.00  SET a.NUMBER = (SELECT distinct b.NUMBER
 0003.00                  FROM LRICEXTWF b
 0004.00                  WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
 0005.00 WHERE EXISTS
 0006.00                 (SELECT distinct b.number
 0007.00                  FROM   LRICEXTWF b
 0008.00                  WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
         ***************************** End of Data ************************************

Still getting SQL0811 ???? Thank you, Bob


From: Bob Schwartz
Sent: Friday, April 10, 2009 2:17 PM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: Newbie SQL problem with UPDATE - SQL0811 - repost


I'm trying to update a file with values from another file using the QM SQL statement below.

I keep getting SQL0811 - Result of select more than one row.

Can someone help me out, I've been all over the place and can't figure this one out.

Actually found some of this searching the archives - Thanks  Elvis B.



Thank you, Bob



        ************************** Beginning of Data *********************************

 0001.00 UPDATE LRACCTWF a  SET a.number   =

 0002.00   COALESCE((SELECT b.number

 0003.00    FROM   LRICEXTWF b

 0004.00    WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)),number)

 0005.00 WHERE EXISTS

 0006.00  (SELECT b.number

 0007.00   FROM   LRICEXTWF b

 0008.00    WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))

         ***************************** End of Data ************************************


Bob Schwartz
Director of Technology Services
Glynn County School System
1313 Egmont Street
Brunswick, GA 31520

912-280-1045 Office
912-223-2924 Cell
912-265-2011 Fax

www.glynn.k12.ga.us
rschwartz@ glynn.k12.ga.us