Delete duplicate entries via SQL?

Question

Is there any possibility in SQL to remove (only one) duplicate entries of composed columns (here: city, zip)? So if i have this SQL:

INSERT INTO foo (id, city, zip) VALUES (1, 'New York', '00000')
INSERT INTO foo (id, city, zip) VALUES (2, 'New York', '00000')

Can i remove the first later with a sql statement? My approach doesn't work for that

DELETE FROM foo (id, city, zip) 
       WHERE id IN 
             (SELECT id FROM foo GROUP BY id HAVING (COUNT(zip) > 1))

Answer 1

Adapted from this article. These two solutions are generic, and should work on any reasonable SQL implementation.

Remove duplicates in-place:

DELETE T1
FROM foo T1, foo T2
WHERE (T1.city = T2.city AND foo1.zip=foo2.zip) -- Duplicate rows
   AND T1.id > T2.id;                           -- Delete the one with higher id

Simple, and should work fine for small tables or tables with little duplicates.

Copy distinct records to another table:

CREATE TABLE foo_temp LIKE(foo);
INSERT INTO foo_temp (SELECT distinct city, zip) FORM foo;
TRUNCATE TABLE foo;

If you're lucky enough to have a sequence as your id, simply:

INSERT INTO foo SELECT * FROM foo_temp;
DROP TABLE foo_temp;

A bit more complicated, but extremely efficient for very large tables with lots of duplicates. For these, creating an index for (city, zip) would incredibly improve the query performance.

Answer 2

It's not clear what SQL is supported in your case, as the different dialects have different features. What comes to my mind is to use a ranking on zip in the inner query instead of HAVING and only include those with a rank > 1.

Answer 3

In SQL Server 2005 and higher:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY city, zip ORDER BY id) AS rn,
                COUNT(*) OVER (PARTITION BY city, zip ORDER BY id) AS cnt
        FROM    mytable
        )
DELETE
FROM    q
WHERE   rn = 1
        AND cnt > 1

to delete the first row (having the duplicates),

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY city, zip ORDER BY id) AS rn
        FROM    mytable
        )
DELETE
FROM    q
WHERE   rn = 2

to delete the first duplicate,

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY city, zip ORDER BY id) AS rn
        FROM    mytable
        )
DELETE
FROM    q
WHERE   rn > 1

to delete all duplicates.

Answer 4

DELETE FROM
  cities
WHERE
  id 
NOT IN
(
    SELECT id FROM 
    (
        -- Get the maximum id of any zip / city combination
            -- This  will work with both duped and non-duped rows
        SELECT 
            MAX(id), 
            city, 
            zip
        FROM
            cities
        GROUP BY
            city,
            zip
    ) ids_only
)