SQL Server – Delete the duplicate record (data) form table

 It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.

To see duplicate record, we can use the count function with group by clause with having in the condition.

 To delete the record to Max function with NOT IN keyword.

Just execute and see how it work to delete duplicate record  into table.

USE tempdb

GO

CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))

Go

INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)

SELECT 1, 'First'

UNION ALL

SELECT 2, 'No Rank'

UNION ALL

SELECT 3, 'Second'

UNION ALL

SELECT 4, 'Second'

UNION ALL

SELECT 5, 'Second'

UNION ALL

SELECT 6, 'Third'

UNION ALL

SELECT 7, 'Five'

UNION ALL

SELECT 8, 'Second'

UNION ALL

SELECT 9, 'Five'

UNION ALL

SELECT 10, 'Nine'

UNION ALL

SELECT 11, 'Third'

GO

-- See the inserted data in create table

SELECT *

FROM Jainendra_TestTable

GO

-- Now below query is detecting duplicate records into table

SELECT Rank_Col, COUNT(*) TotalCount

FROM Jainendra_TestTable GROUP BY Rank_Col

HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

GO

-- Now below query is deleting the duplicate record into table

DELETE FROM Jainendra_TestTable

WHERE My_ID NOT IN

( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)

GO

-- Selecting Data

SELECT *

FROM Jainendra_TestTable

GO

DROP TABLE Jainendra_TestTable

GO

If it is useful than please like and share it to other SQL Server learners