T SQL to clean up tables
Posted on 2011-05-10
I have a database table which is in a bit of a mess and I would like to do some cleanup.
Essentially I have a table ijob and a table iinvoice - ijob contains a field iinvoice_id which points to a record in the iinvoice table or may be NULL.
Each record in the iinvoice table has a field iinvoice_number which is a string.
My problem is that I have duplicate records in the iinvoice table - that is records with the same iinvoice_number pointed at by the ijob table.
As an example - I might have 3 iinvoice records with iinvoice_id set to 100, 101 amd 102 but all with the same iinvoice_number. In the ijob table I have jobs pointing to 100, 101 and 102.
What I would like is a query which will update ijob so that they link to the first matching entry in the iinvoice table and preferably delete the old records which are no longer required.
I have made a start(?) with
SELECT MIN(iinvoice_id),COUNT(iinvoice_number) AS DUPLICATE FROM iinvoice GROUP BY iinvoice_number ORDER BY DUPLICATE DESC
which identifies where there are duoplicates but not sure where to go next