CODEWITHSUNDEEP
sqlt-sql
Aiden
Aiden

Reputation: 139

SQL Delete duplicate rows in the table without primary key on SQL Server

This is the table I want to delete duplicate

So I want to DELETE the duplicate rows not just FIND IT My current code can only let me know which ROWS have to be deleted. But I am confusing how to "DELETE" them from my target table. Any suggestion? 

WITH tmp AS
(
    SELECT
     Code
    ,ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS ROWNUMBER
    FROM CouponCode
)

SELECT *
FROM tmp
WHERE ROWNUMBER > 1

Upvotes: 1

Views: 2913

Answers (2)

GSazheniuk
GSazheniuk

Reputation: 1384

Step 1:

Select distinct rows into temporary table:

SELECT DISTINCT Code, ExpiredDate 
INTO temp_CouponCode
FROM CouponCode

Step 2:

Empty original table:

truncate table CouponCode

Step 3:

Copy data from temporary table:

INSERT INTO CouponCode
SELECT Code, ExpiredDate 
FROM temp_CouponCode

Step 4:

Remove temporary table:

DROP TABLE temp_CouponCode

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You just change your select to a delete, basically:

WITH tmp AS (
      SELECT Code, ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS ROWNUMBER
      FROM CouponCode
)
DELETE tmp
    WHERE ROWNUMBER > 1;

Upvotes: 3

Related Questions