RE: Newbie SQL problem with UPDATE - SQL0811 - repost

Elvis, Charles, Jim and everyone, thank you all so much for your help.

Elvis you hit with your duplicate records:
===== BEGIN ELVIS WROTE ============================================================
So, you need to ask yourself, how can a single student have multiple school
numbers in the LRICEXTWF file?
You can see which students these are by doing something like this:

select substr(stude00001,1,5) as studentPIN, count(*)
from LRICEXTWF
group by substr(stude00001,1,5)
having count(*) > 1

If you can't enhance the join so it guarantees a one-to-one mapping, can you
delete some of these 'duplicate' values?
===== END ELVIS 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.


Bob Schwartz
Director of Technology Services
Glynn County School System
1313 Egmont Street
Brunswick, GA 31520
www.glynn.k12.ga.us