×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
As an Amazon Associate we earn from qualifying purchases.
This thread ...
Re: Newbie SQL problem with UPDATE - SQL0811 - repost, (continued)
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.