Home » Interview Q & A » Q3. How to DELETE duplicate records from a table?

Q3. How to DELETE duplicate records from a table?

Our Categories

Blog Stats

  • 34,479 hits

Calendar

May 2013
M T W T F S S
« Apr   Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Please visit SQLVERSITY.COM for more details.

Let us say, duplicated records are there in a table with the same values for all the columns.

Now we have to delete 2 duplicated rows from the below record set.

Since all the column values are same, we can’t use WHERE condition in this case. This can be possible by using ROW_NUMBER() function.

Remove Duplicated records

Any of the two records should be deleted out of three records in the above table.

WITH DeleteDup
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID) AS RowNum
FROM dbo.DuplicatedEmployee
)

-- Delete the duplicate records.
DELETE FROM DeleteDup WHERE RowNum > 1

-- Now Check the data.
SELECT * FROM dbo.DuplicatedEmployee
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: