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