Remove duplicates

[AV1611]

I have a table with a lot of duplicate records...

 

Is there an easy way to purge all TRUE duplicates?

 

or do I need to write a script to do it?

[MadTechie]

Well this will find the dups (for email)

 

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

 

[PC Nerd]

well id do ....

 

 

$records = array();
$same = 0;

select * from table

while $row = mysqli_fetch_array{

$records[] = $row
}






select * from table

while $row = mysql_fetch_array($query) {


foreach $row as $field => $value {

foreach($records as $record)

if($row[$field] == $record[field]) {
         $same = 1;

}

if($same == 0)       {

break;

}

}

if($same == 1) { delete_duplicate($row['id']); }

$records[] = $row


}

 

 

what it does is loops through every field in the database and checks every field in each row, against all the other rows......... then if all the fields are the same then call a function that you create, that deltes the duplicate

 

 

howver thats only rough, dont rely on it though

[pocobueno1388]

Well this will find the dups (for email)

 

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

 

 

Wouldn't the code have to be:

FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

 

So it could find the records that are GREATER than 1? You put equal...not sure which one it would be.

 

[AV1611]

is

 

create temporary table distinctrecords(select distinct * from oldtable);

delete from oldtable;

insert into oldtable (select * from distinctrecords)

 

be valid?