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

Question

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

You could simply select into a temp table, clear out the existing table and insert into the now cleaned table all results from the temp table. — Jako Basson
– Jako Basson, Commented Jul 25, 2018 at 21:15
@JakoBasson yes you are correct. What I am thinking is if we have a millions of data rows in the table, it could be slow if we copy data to a new table and rebuild later. — Aiden
– Aiden, Commented Jul 25, 2018 at 21:41

Gordon Linoff · Accepted Answer · 2018-07-25 21:19:54Z

3

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;

answered Jul 25, 2018 at 21:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aiden Over a year ago

Wow..I didn't know that CTE can let me modify original table directly. Can I say CTE is the same as VIEW in this point, since we can also modify original table by modifying view

Gordon Linoff Over a year ago

@Aiden . . . Yes. Both CTEs and Views have the same rules for being updatable (well, you can define a trigger on a view as well, but that is another matter).

GSazheniuk · Accepted Answer · 2018-07-25 21:20:54Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related