Deleting similar or identical records in SQL is a lifesaver. I found Brian Cryer’s “SQL How To…” about deleting similar or identical records in SQL. It’s got a lot of information, including the code for MS-Access SQL.
Here’s a quick explanation of what the following code does:
It searches MyTable for and deletes all rows where the dupField is the same except for the row with the lowest uniqueField.
Delete Similar Records
To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:
DELETE T1
FROMÂ MyTable T1, MyTable T2
WHEREÂ T1.dupField = T2.dupField
AND T1.uniqueField > T2.uniqueField
To break this down even further, this code would delete all rows in MyTable that had the same value in dupField.
DELETET1
FROM MyTable T1, MyTable T2
WHERE T1.dupField = T2.dupField
This last line restricts the deleting to only rows that are also greater than another row with the same dupField
AND T1.uniqueField > T2.uniqueField
Obviously, you would replace the field and table names to work for your table.
Talk soon,
Ashton Sanders
1 Comment
Great!
I also saw this post. Howeve, if we modify it in the following way:
DELETE T1
FROM MyTable T1, MyTable T2
WHERE T1.dupField = T2.dupField AND T1.dupField2=T2.dupField2
AND T1.uniqueField > T2.uniqueField
And if dupField2 is NULL, then it does not work. Any suggestions