Sql server delete records that have a single duplicate column

This article is tagged with: Sql server delete records that have a single duplicate column and mysql

query delete duplicates

DELETE FROM dups a USING (
SELECT MIN(ctid) as ctid, key
FROM dups
GROUP BY key HAVING COUNT(*) > 1
) b
WHERE a.key = b.key
AND a.ctid <> b.ctid

sql server delete records that have a single duplicate column

WITH cte AS (
    SELECT
        contact_id,
        first_name,
        last_name,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY
                first_name,
                last_name,
                email
            ORDER BY
                first_name,
                last_name,
                email
        ) row_num
     FROM
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

This article is tagged with: Sql server delete records that have a single duplicate column and mysql