Daily Archives: Apr 30, 2018

Find and Delete all duplicate rows but keep one

In this post “Find and Delete all duplicate rows but keep one”, we are going to discuss that how we can find and delete all the duplicate rows of a table except one row. Assume that we have a table named tbl_sample which has four columns – EmpId, EmpName, Age, and City. This table has some duplicate data (in all the four columns) which needs to be deleted except the original one row. To demonstrate this, let’s create the dummy table with some sample data.

Here is the code to create the dummy table with sample data:

IF OBJECT_ID('dbo.tbl_Sample') IS NOT NULL
	DROP TABLE dbo.tbl_Sample

CREATE TABLE dbo.tbl_Sample
	EmpId INT,
	EmpName VARCHAR(256),
	City VARCHAR(50)

INSERT INTO dbo.tbl_Sample
(EmpId, EmpName, Age, City)
VALUES('1', 'Smith', 30, 'New York'),
('1', 'Smith', 30, 'New York'),
('1', 'Smith', 30, 'New York'),
('2', 'Adam', 35, 'California'),
('2', 'Adam', 35,