CODEWITHSUNDEEP
sqlsql-serverduplicatesno-duplicates
leora
leora

Reputation: 196881

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?

Upvotes: 8

Views: 15026

Answers (2)

M.Ali
M.Ali

Reputation: 69594

;WITH CTE
AS (
    SELECT PosId
          ,OrgId
          ,ROW_NUMBER() OVER (PARTITION BY PosId , OrgId ORDER BY PosId , OrgId) rn
    FROM   PosOrg
   )
DELETE FROM CTE
WHERE rn > 1

Upvotes: 7

juergen d
juergen d

Reputation: 204924

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
)

Upvotes: 12

Related Questions