This conversation reminds me of a 'helpful' little 'feature' in MS Access. Their advice for deleting duplicate records
isto create a copy of the table with unique index on the fields that you don't want to be duplicated, then insert into
table2select * from table1. They will happily delete all but one of them for you. I don't know if it's the first,
last,or a random record that they keep.
Microsoft Access,You Don't Have to Know What You are Doing (TM).
>>> "Greg Sabino Mullane" <greg@turnstep.com> 02/21/02 05:55AM >>>
-----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-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
