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?
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
)
;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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With