Updating tables A,B&C if duplicates in table A
-
I have programmed RPG, for 20 years and just now learning SQL. So you can imagine the changes I'm going through. I searched the web for answers to the following: and need help I can identify the duplicates . I can update single tables. Beyond that I'm not sure, the set theory is vastly different. I'm told I need a cursor to process row by row Any help?
SELECT FIELDA, FIELDB, FLDR
FROM TableA c
WHERE c.FLDR < (
SELECT MAX (FLDR ) FROM TableA
WHERE FIELDA = c.FIELDA
AND FIELDB = c.FIELDB)
order by FIELDA, FIELDB, FLDR
I need to identify the duplicates in TableA of FIELDA FIELDB, then use the lowest FLDR of those duplicates in TableA to update the FLDR in TableB and TableC , then delete duplicates in TableA with FLDR higher than the one used for the update
TableA
FLDR FIELDA FIELDB
400897 TR 42444
448117 TR 42444
440182 TR 42444
401010 GA 12345
401898 GA 12345
404054 GA 777888
TableB
FLDR PARR
400897 75676
448117 75676
401010 60111
404054 603040
TableC
FLDR PARR DOCR
400897 75676 12
448117 75676 15
440182 23
401010 60111 100
401898 49
404054 603040 54
.
Data Should look like this when finished.
TableA
FLDR FIELDA FIELDB
400897 TR 42444
401010 GA 12345
404054 GA 777888
TableB
FLDR PARR
400897 75676
400897 75676
401010 60111
404054 603040
TableC
FLDR PARR DOCR
400897 75676 12
400897 75676 15
400897 23
401010 60111 100
401010 49
404054 603040 54
Changinagain
-
There was a Microsoft Tech Net article that had a very easy process to deal with dupes, unfortunately I can't find it and I have no other reference off-hand.
The basic process was as follows:
1. Identify dupes through a GROUP BY/HAVING COUNT(*) > 1
2. Insert unique occurances of these into a new table. This is a complete copy of all fields. So this temp table holds one instance of each key value.
3. Delete all dupes from your first table
4. Insert the contents of the temp table
I hope this helps. And congrats of escaping RPG! 🙂 I studied it 20+ years ago and was quite impressed when the instructor told us that the compiler (on an MP/M machine with 8" floppies) would always produce errors, you just had to code/modify until you got no fatals and then see if it runs.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply