Re: deleting an identical record

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