Aleks
asked on
SQL to delete duplicate records
I have a table 'userlogin' that has duplicates or tripiliates, even more for the 'loginid' field. And we need to clean it up and leave only the most recent.
There are two relevant fields:
The 'userloginid' is a consecutive number. (Autoid), the higher the number the most recent the entry
And the 'Loginid' field which is the one we need to check for duplicates.
I need an SQL that will delete any entries that are duplicates (or more) and leave only one of those entries, leaving the one where the 'userloginid' has the higher number (Most recent)
Any entries without duplicates should be left as is, we should not delete those. Only dup entries.
Any help is appreciated.
There are two relevant fields:
The 'userloginid' is a consecutive number. (Autoid), the higher the number the most recent the entry
And the 'Loginid' field which is the one we need to check for duplicates.
I need an SQL that will delete any entries that are duplicates (or more) and leave only one of those entries, leaving the one where the 'userloginid' has the higher number (Most recent)
Any entries without duplicates should be left as is, we should not delete those. Only dup entries.
Any help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Zakaria,
Almost exactly what I posted,
Regards
David
Almost exactly what I posted,
Regards
David
try this.
;WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Loginid ORDER BY userloginid DESC) rn
FROM your_table)
DELETE FROM CTE WHERE rn > 1
ASKER
The first script did something but there were some duplicates left, the second one deleted all duplicates.
I will double check to make sure it only deleted duplicates, otherwise this was the simple and correct solution for what was needed.
I appreciate the input from all.
I will double check to make sure it only deleted duplicates, otherwise this was the simple and correct solution for what was needed.
I appreciate the input from all.
Take a look at this code, which I think does what you need.
Regards
David
Open in new window