In SQL, how can I delete duplicate rows based on multiple columns?
11,916
Solution 1
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
)
Solution 2
;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
Author by
leora
Updated on June 09, 2022Comments
-
leora almost 2 years
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?