Saturday, December 29, 2012

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

Like and Share to SQL Integrity Blog