Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.
SQL query to detect and delete duplicate records
I need a SQL query to detect and delete duplicate records, that is records where firstname and lastname are identical (if the case is different, would still be duplicate). Detection and deletion could be in separate steps.
Thanks
Thanks
Asked:
2
- +1
1 Solution
If no primary key, try something like this :
SELECT DISTINCT firstname, lastname
INTO #temp_table
FROM source_table
DELETE FROM source_table
INSERT INTO source_table
SELECT * FROM #temp_table
SELECT DISTINCT firstname, lastname
INTO #temp_table
FROM source_table
DELETE FROM source_table
INSERT INTO source_table
SELECT * FROM #temp_table
do you have a case sensistivity problem ?
if so convert both names to upper case and then do the test....
if so convert both names to upper case and then do the test....
Agree with lowfat--if you have something you can use for a key, you're better off to use that method. If not, you need to use a method like BillAn1 suggested (I would just truncate the table instead of deleting the old rows--also, if you have a lot of data, this can be very slow) or a cursor.
Here is a sample using a cursor but that does not require a separate table or a dump/reload:
DECLARE dupsCsr CURSOR READ_ONLY FOR
SELECT [firstName], [lastName], COUNT(*) AS numDups
FROM yourTable
GROUP BY [firstName], [lastName]
HAVING COUNT(*) > 1
DECLARE @firstName VARCHAR(30) --Change to match datatype on your table
DECLARE @lastName VARCHAR(30) --Change to match datatype on your table
DECLARE @numDups INT
OPEN dupsCsr
FETCH NEXT FROM dupsCsr INTO @firstName, @lastName, @numDups
WHILE @@FETCH_STATUS = 0
BEGIN
SET @numDups = @numDups - 1 --delete all but 1 of the duplicates
SET ROWCOUNT @numDups
DELETE FROM yourTable
WHERE [firstName] = @firstName AND [lastName] = @lastName
FETCH NEXT FROM dupsCsr INTO @firstName, @lastName, @numDups
END --WHILE
CLOSE dupsCsr
DEALLOCATE dupsCsr
SET ROWCOUNT 0 --restore default
DECLARE dupsCsr CURSOR READ_ONLY FOR
SELECT [firstName], [lastName], COUNT(*) AS numDups
FROM yourTable
GROUP BY [firstName], [lastName]
HAVING COUNT(*) > 1
DECLARE @firstName VARCHAR(30) --Change to match datatype on your table
DECLARE @lastName VARCHAR(30) --Change to match datatype on your table
DECLARE @numDups INT
OPEN dupsCsr
FETCH NEXT FROM dupsCsr INTO @firstName, @lastName, @numDups
WHILE @@FETCH_STATUS = 0
BEGIN
SET @numDups = @numDups - 1 --delete all but 1 of the duplicates
SET ROWCOUNT @numDups
DELETE FROM yourTable
WHERE [firstName] = @firstName AND [lastName] = @lastName
FETCH NEXT FROM dupsCsr INTO @firstName, @lastName, @numDups
END --WHILE
CLOSE dupsCsr
DEALLOCATE dupsCsr
SET ROWCOUNT 0 --restore default
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
select 'matched to ', t.pk, d.*
from Table as D
inner Join Table as T
on D.Pk < T.PK
and D.Firstname=T.FirstName
and D.Lastname=T.lastName
you need criteria to decide which one to delete
Delete from Table
Where Exists (Select T.pk from table as T
Where T.pk > Table.pk
and T.firstname=Table.firstnam
and t.lastname = table.lastname)
I hope this isn't HOMEWORK ?