DELETE duplicate rows from table

–Delete All Duplicate Records:

DELETE FROM DUP_TBL
WHERE id1 IN
(SELECT id1 FROM DUP_TBL
GROUP BY id1 HAVING COUNT(id1) > 1)

OR

–DELETE Duplicate Records

WITH temp AS
(SELECT id1,name1, row_number() over(partition by id1 order by id1) as duplicates from dup_tbl)
delete from temp where duplicates > 1

–DELETE DUPLICATE RECORDS FROM TABLE (Composite Primary Key)

USE [Northwind]
WITH temp AS
(SELECT CustomerId, ContactName, row_number()
OVER(PARTITION BY CustomerId, ContactName ORDER BY CustomerId) as duplicates
FROM Customers)
DELETE FROM temp WHERE duplicates > 1

Advertisements

About Gokul Dahal, Nepal

Software Engineer, Freelancer, Design and Develop web applications,Software Developer Nepal, FreeLancer Nepal Contact : gokuldahal@gmail.com
This entry was posted in SQL and tagged , , , , . Bookmark the permalink.

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