'Is there a way to delete duplicates from a table with a single column in SQL Server?

Is there a possible way to delete duplicate rows from a table like this without dropping the table and re-creating it with distinct rows?

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp (id INT);

INSERT INTO #temp
VALUES (1), (2), (2), (2), (3), (3);
sqlsql-server


Solution 1:[1]

Using a CTE and ROW_NUMBER grouped by the specified column you are looking at can cleanly remove duplicate values, as ROW_NUMBER counts incrementally with duplicate values of the PARTITION BY aggregation. When a new grouping is found when ordered, it resets the ROW_NUMBER to 1 starting at that next record of different grouped values.

 WITH CTE AS (
 SELECT *,
 ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RN
 FROM #temp
 )

 DELETE FROM CTE WHERE RN<>1

Solution 2:[2]

Here is your answer, First we have to find all recodes

DELETE t 
FROM #temp t 
WHERE (SELECT count(1) 
    FROM #temp t1 
    WHERE t.id =t1.id )
    >1

Hope you will find the best solutions.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Vipul Zadafiya

Related Questions