Red Devil Red Devil - 2 months ago 5
SQL Question

Need to delete duplicate records from the table using row_number()

I am having a table test having data as follows and I want to delete the trsid 124 and I have millions entry in my DB it is just a scenarion. Concept is to delete the duplicate entry from the table

--------------------------------------------
TrsId | ID | Name |
--------------------------------------------
123 | 1 | ABC |
124 | 1 | ABC |


I am trying something like

delete from test
select T.* from
(
select ROW_NUMBER() over (partition by ID order by name) as r,
Trsid,
ID,
name
from test
) t
where r = 2


Even if I update the query which is Ok for me

update test set id=NULL
select T.* from
(
select ROW_NUMBER() over (partition by ID order by name) as r,
Trsid,
ID,
name
from test
) t
where r = 2


But if i run both this query it deletes all the records from table test. And if i update it update both the records.
I dont know what I am doing wrong here

Answer
WITH cte AS
(
    SELECT ROW_NUMBER() OVER(PARTITION by ID ORDER BY name) AS Row
    FROM test
)

DELETE FROM cte
WHERE Row > 1
Comments