Re: Newbie SQL problem with UPDATE - SQL0811

Hi Bob
try the following
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))


notice I took out the coalesce


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 04/10/2009 01:15:17 PM:

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.

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
************************************
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.