Re: Newbie SQL problem with UPDATE - SQL0811 - repost

Hey Tom, I almost gave up and did it in RPG, but I was determined to use SQL!
And, with everyone's help I was able to do it.

Thanks!


date: Fri, 10 Apr 2009 14:38:59 -0700
from: Tom Liotta <qsrvbas@xxxxxxxxxxxx>
subject: Re: Newbie SQL problem with UPDATE - SQL0811 - repost

Bob Schwartz wrote:

And guess what ... I have a bunch of students with two records/same PIN in LRICEXTWF.
One for type of service equal P=Primary and another record for type of service S=Secondary.

I ended up doing this:

UPDATE LRACCTWF a
SET a.NUMBER = (SELECT b.NUMBER
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)
and SERVI00001='P')
WHERE EXISTS
(SELECT *
FROM LRICEXTWF b
WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)
and SERVI00001='P')


Which allowed the SQL statement to run w/o errors because I omitted the Secondary service records.

Way to go gang! I can't thank you enough, maybe I can contribute something of value in the future.

I'd love to see how the time/effort to reach this point would
compare to creating a simple RPG program to do the same thing.

I'd probably do it in SQL, too; but I still have to wonder.

Tom Liotta