Re: deleting an identical record

This conversation reminds me of a 'helpful' little 'feature' in MS Access. Their advice for deleting duplicate records is to create a copy of the table with unique index on the fields that you don't want to be duplicated, then insert into table2 select * 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(at)turnstep(dot)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(at)turnstep(dot)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