0

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.

Corey Williams
  • 17
  • 1
  • 5

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786