List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 7 1999 5:01pm
Subject:Re: duplicates
View as plain text  
On Tue, 1999-09-07 09:46:26 -0700, Linsen Limsico wrote:
> Does any one know what statement to use to search and remove
> duplicates from the database?

1st, you have to say what kind of duplicate you're looking for,
     duplicates in respect to which fields ...

     For example, if you have a table 't' with fields (f1,f2,f3)
     and want to find all groups of rows that are identical in
     (f2,f3), then just do a self join of the table with itself:

     SELECT *
     FROM t, t as t2
     WHERE t.f2=t2.f2 AND t.f3=t2.f3;

2nd, now you must decide which records to keep!
     ... and delete the other ones.
     This has to be a 2nd SQL statement, anyway, because MySQL
     can only use a single table with DELETE.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
RE: duplicatesLinsen Limsico7 Sep
  • Re: duplicatesMartin Ramsch7 Sep
  • RE: duplicatesRicardo T. Saito7 Sep