Monthly Archives: April 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
GO

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

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, 
More