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
– Brian DeMilia, Commented Aug 24, 2014 at 23:49

juergen d · Accepted Answer · 2014-08-24 23:45:43Z

12

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, 2014 at 23:45

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

leora Over a year ago

wouldn't i need a "where > 1" somewhere on this?

juergen d Over a year ago

No, you delete all records that are not min(id) for every group, meaning all duplicates

Brian DeMilia Over a year ago

@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.

M.Ali · Accepted Answer · 2014-08-24 23:50:38Z

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, 2014 at 23:50

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related