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

Question

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?

What are the other columns on that table? Is there a PK constraint? — Brian DeMilia, Aug 24 '14 at 23:49

score 10 · Accepted Answer · answered Aug 24 '14 at 23:45

10

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
)

answered Aug 24 '14 at 23:45

juergen d

201,996
37
293
362

1

wouldn't i need a "where > 1" somewhere on this? – leora Aug 24 '14 at 23:51
No, you delete all records that are not `min(id)` for every group, meaning all duplicates – juergen d Aug 24 '14 at 23:52
@leora no, not if there is a unique field called ID on the table. You should add to your question whether or not there is such a field. – Brian DeMilia Aug 24 '14 at 23:53

score 7 · Answer 2 · answered Aug 24 '14 at 23:50

7

;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

answered Aug 24 '14 at 23:50

M.Ali

67,945
13
101
127

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

2 Answers2

Linked

Related