How to find duplicates from Unique code column and delete the rows they’re attached too, while still keeping the original row?

I have a table in my azure sql server named dbo.SQL_Transactional, and there are columns with headers as code, saledate, branchcode

code is my primary key, so if there is ever 2 or more rows with the same code, they are duplicates and need to be deleted. How can I do so? I don’t need to worry about if saledate or branchcode are duplicates, because if the code is duplicated then that’s all I need to be able to delete the entire duplicate row.

Answer

If you just want to delete the duplicate rows, then try an updateable CTE:

with todelete as (
      select t.*, row_number() over (partition by code order by code) as seqnum
      from t
     )
delete from todelete
    where seqnum > 1;

If you just wanted to select one row, then you would use where seqnum = 1.