Search code examples
sqlsql-serverduplicatesno-duplicates

In SQL, how can I delete duplicate rows based on multiple columns?

I know I can run the following query below to find "duplicate" rows based on multiple columns doing something like this:

SELECT      PosId, OrgId
FROM        PosOrg
GROUP BY    PosId, OrgId
HAVING COUNT(*) > 1

but now I want to delete the duplicate rows so the above query ends of returning zero rows. I don't care which of the rows that I delete (just as long as only one rows remains based on the uniqueness of those two columns.

What is the correct way to delete these duplicates in SQL?

Solution

  • If you have another unique id column you can do

    delete from PosOrg
where id not in
(
  SELECT      min(id)
  FROM        PosOrg
  GROUP BY    PosId, OrgId
)