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))

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.

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.

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.

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
)

The accepted answer didn't work on my oracle db. This did:

DELETE FROM
   mytable A
WHERE
  A.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        mytable B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );

(Also works for any column instead of rowid.)

Found here.