Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 645
leora Avatar asked Dec 14 '22 20:12

leora


2 Answers

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
)
like image 137
juergen d Avatar answered Dec 29 '22 00:12

juergen d


;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
like image 33
M.Ali Avatar answered Dec 29 '22 00:12

M.Ali