jason jason - 29 days ago 13
SQL Question

Remove duplicate entries without primary key in SQL

I have this table MSSQL :

EntityNo int
City nvarchar(MAX)
Province nvarchar(MAX)
EntityDate datetime


EntityNo should be unique, but it's not primary key. How can I remove one of the duplicate entries from this table? Thanks.

Answer

Use this query

DELETE a FROM (
    SELECT row_number() over(partition by EntityNo order by EntityNo) as RowNo
    FROM Entity
) AS a WHERE RowNo > 1