Re: deleting an identical record

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> In a table I entered by accident two times the same record, if I now
> list the table I see two lines with exactly the same contents. How can
> I delete one of those two records? I can't find a select criterium which
> differs for both.

CREATE TABLE t (foo INT, bar INT);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);

To delete just one of the duplicates:

DELETE FROM t WHERE oid = (SELECT oid FROM t WHERE foo=2 AND bar=4 LIMIT 1);

To delete the last one added:

DELETE FROM t WHERE oid =
  (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1);

Unfortunately, the above will also delete a single record with those
conditions, so we should make sure there are at least two records first:

DELETE FROM t WHERE oid =
  (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1)
  AND oid !=
  (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC  LIMIT 1);

but that gets a little ugly. Why not delete all but the first one we added?:

DELETE FROM t WHERE foo=2 AND bar=4 AND
  oid != (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC LIMIT 1);

The above should work for all cases.

HTH,

Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200202210848

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8dPvyvJuQZxSWSsgRAohaAJoCssDevWjvWRRB5Qwse7XJrGUp0gCgz1nI
okDJcYTpVLjiRv8+zYlYlb0=
=WEOm
-----END PGP SIGNATURE-----