How to Delete Duplicate Records from Table

Below query is useful to delete the duplicate Ids data from table. The table must have identity column, which will be used to identify the duplicate records.

CREATE TABLE StudentDetail(ID INT IDENTITY(1,1),StudentID INT,Name VARCHAR(10),Address VARCHAR(100))

INSERT INTO StudentDetail
SELECT 10001,’Test1′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10002,’Test2′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10003,’Test3′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10004,’Test4′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test5′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10001,’Test11′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test51′,’Mumbai’

;WITH CTE
AS
(
SELECT ID,ROW_NUMBER()OVER(PARTITION BY StudentID ORDER BY ID ASC)DuplID
FROM StudentDetail
)
DELETE FROM CTE WHERE DuplID > 1

SELECT * FROM StudentDetail;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: