Matt,
If you went with option #3, you could avoid the looping by using (tCount - 1)
as the LIMIT in the delete statement instead of always using 1.
Regards,
Nathan
-Original Message-
From: Matt Neimeyer [mailto:m...@neimeyer.org]
Sent: Tuesday, July 14, 2009 8:58 AM
To:
You can combine the two queries you have in option 3 (you'll need to
change field names, but you should get the idea), something like this:
DELETE table1 FROM table1, (SELECT MAX(id) AS dupid, COUNT(id) AS
dupcnt FROM table1 WHERE field1 IS NOT NULL GROUP BY link_id HAVING
dupcnt1) AS dups
That's assuming that there is a unique identifier field, like an auto
increment field. Although that could be added after the fact. Also,
you need to run the query multiple times until it returns no affected
records. So if there are 4 copies of a record, it would need to be run
3 times to get rid
Ah... Yes. Good point. I like this because I was planning on keeping
the output somewhere for a while. (In case we need an accounting at
some point) So it will be easy enough to dump what's being deleted to
the screen while we loop over our candidates.
Thanks!
On Tue, Jul 14, 2009 at 10:16 AM,
If you use IGNORE in the insert IGNORE into new_table you will get the
result you want.
-Original Message-
From: walt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 3:11 PM
To: David Kramer; [EMAIL PROTECTED]
Subject: Re: removing duplicate records
Thanks David
If the columns are defined as not null, just add a unique index on all
the fields.
alter table ignore add unique(col1.col2.col#);
walt wrote:
Does anyone know a good way find and remove duplicate records from a table?
I can create an identical table structure and use a script to pull records
You could always use an insert statement into a second table, when
performing the insert use a GROUP BY clause to consalidate your records
something along these lines but this isnt 100% accurate, I would need the
table DDL and business rules behind the DEDUP
Insert into tableB
(
column
Thanks David!
The entire row, not just one or two columns, is a duplicate which makes life
fun..
:-)
I can key or unique index only a few columns once the data is cleaned up to
prevent this problem.
If I create an identical table and include either a key or unique index
(innodb seems to
Walt,
Don't trust group by.
I am using mysqldump and sort -u on *nix for duplicate.
Try both ways, let me know your result.
Thanks
At 04:11 PM 7/30/2002 -0400, walt wrote:
Thanks David!
The entire row, not just one or two columns, is a duplicate which makes life
fun..
:-)
I can key or unique
-Original Message-
From: Vivian Wang [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 2:37 PM
To: walt; David Kramer; [EMAIL PROTECTED]
Subject: Re: removing duplicate records
Walt,
Don't trust group by.
I am using mysqldump and sort -u on *nix for duplicate.
Try both ways, let me
10 matches
Mail list logo
