asked on

Delete duplicate rows sql server

Lets say you have a table called test with the follow information

firstname lastname gender email
steve jones m steve@yahoo.com
steve jones m jones@yahoo.com
steve jones m other@yahoo.com
steve jones f steve@yahoo.com

You want to delete duplicate records where firstname = steve and lastname = jones and gender = m, you want to leave one record, it would not matter which one was left even though there are three different email addresses it does not matter just as long as you can delete duplicates of those 3 columsn so you would be left with something like this

firstname lastname gender email
steve jones m steve@yahoo.com
steve jones f steve@yahoo.com

how would you do this in sql server

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

Alternate way

Remove all duplicities:
SELECT firstname, lastname, gender, MIN(email) AS email INTO #tmpTbl FROM test GROUP BY firstname, lastname, gender
TRUNCATE TABLE test
INSERT INTO test SELECT * FROM #tmpTbl

Remove Jones Steve duplicity independently on gender:
SELECT firstname, lastname, gender, MIN(email) AS email INTO #tmpTbl
FROM test
WHERE firstname = 'steve' AND lastname = 'jones'
GROUP BY firstname, lastname, gender

DELETE FROM test WHERE firstname = 'steve' AND lastname = 'jones'

INSERT INTO test SELECT * FROM #tmpTbl