Delete duplicate entries?

Anon

Anyone know if it's possible to delete almost duplicate entries? Like if I have a table with 4 fields, one of which is an ID (auto_increment), is there a standard algorithm to delete rows that match the 2nd,3rd, and 4th values of a particular row? I can just manually search for them, but there are about a million entries so if there's a method to accomplish this task that you know of, please inform me.

Thanks,

Mike

smarlowe

The easy way is like this:

select distinct field1, field2, field3 into some_table from some_other_table;
drop table some_other_table;
alter table some_table rename to some_other_table;

Note that if you need to keep the ids from the source table, things get a little more difficult, since you've got two ids for one row in the desitnation. In that case you can group by an aggregate of the fields you want and select max(id) to decide and use that in a subselect. It's been a while since i did that, i'd have to play in psql a while to get it to work.

Anon

Here's a php method of doing...probably no better.

<?php

$query = "SELECT id,field1,field2,field3 FROM table";
$results = mysql_query($query);

while ($myrow = mysql_fetch_array($results)) {

$id = $myrow["id"];
$field1 = $myrow["field1"];
$field2 = $myrow["field2"];
$field3 = $myrow["field3"];

$qur_2 = "DELETE FROM table WHERE field1='$field1' AND field2='field2' AND field3='$field3' AND id != '$id'";

$qry_res = mysql_query($query);

}

Just another method...kinda silly 🙂

Anon

oops...last query() is $qur_2

Anon

Actually I like your method--doesn't require any second tables or anything. 🙂

Mike

smarlowe

I'm not certain, but I think this one will delete all the duplicate rows leaving only rows that didn't have twins.

If you have this:

fname|lname|ext|id
bob|smith|123|1
bob|smith|123|2
john|jones|534|3

and you ran this loop, the first row would delete the second row, then the second row, still in out result set, would delete the first row. You'd need to add a check to see which rows you'd deleted so you wouldn't delete the first occurance.

[deleted]

Scott's method does require an extra table, but it also defragments your table while you are removing the duplicates. Added-bonus :-)

Anon

Hi!
I just want to know what formula to be use the delet prg.
Pls. help me
even thu its simple pls. because
I pass it in monday.
Thank u very much

smarlowe

The method I posted works fine (tested and true) and is portable to most SQL type databases just fine.

Anon

I have tried this method, but cannot get the "select distinct..." line to work. Are there some special requirements for the table into which i am "selecting distinct"? I have made a table with exactly the same columns... but i can't get it to transfer teh data from one table to another.

help!

thanks

=)Caroline

Anon

That's not the correct SQL format for copying content from one table to another.

The correct format is:
INSERT into NEWTABLE select * from OLDTABLE

or do get rid of duplicates:
INSERT into NEWTABLE select distinct field1, field2, field3 from OLDTABLE

Make sure you have created the new table first, and that it mimicks the old table perfectly.

Then make a unique index on all the fields that should be unique:
ALTER NEWTABLE add unique index(field1, field2, field3)

--Viral