Deleting Duplicated Entries

[Kryptix]

I have a table called 'rscd_experience'

 

In this table I have a field called 'user' which is a hashed username, as well as several other fields.

 

I need a query to search for duplicate 'user' and then delete one of the entries. There's about 1,200 duplicates at the moment.

 

I have no idea where to start with this. Could someone write a rough query so I could learn from it please?

[DavidAM]

What is the layout of the table?  Is there some other column that is unique or is likely to be unique?

[Kryptix]

What is the layout of the table?  Is there some other column that is unique or is likely to be unique?

Nope.

 

It has 'user' which is a hash, and then like 18 other columns storing their experience in each stat.

 

As it's duplicated, when it does the UPDATE query it updates both of them, using resources I don't want it to. :-\

[DavidAM]

I don't know any way to do it in a single sql statement.  However, you could build a new table without the duplicate data, drop the old table and rename the new table.  I would suggest that you add a unique index on the new table so you don't have this problem again in the future.

 

CREATE TABLE TestDelNew LIKE TestDel;

INSERT INTO TestDelNew SELECT DISTINCT * FROM TestDel;

RENAME TABLE TestDel TO TestDelOld, TestDelNew TO TestDel;

DROP TABLE TestDelOld;

[Kryptix]

I don't know any way to do it in a single sql statement.  However, you could build a new table without the duplicate data, drop the old table and rename the new table.  I would suggest that you add a unique index on the new table so you don't have this problem again in the future.

 

CREATE TABLE TestDelNew LIKE TestDel;

INSERT INTO TestDelNew SELECT DISTINCT * FROM TestDel;

RENAME TABLE TestDel TO TestDelOld, TestDelNew TO TestDel;

DROP TABLE TestDelOld;

That looks perfect, but I have just tried and the 'INSERT INTO' statement copied the entire table, even duplicate entries. I unique column is called 'user', there should only be one 'user' the same. The other 18+ rows could be the same as others, but I need to delete all duplicate entries where 'user' isn't unique.

 

Mainly thanks for your help.

[Kryptix]

Anyone? I'm sure it's really simple for a lot of you...

[Linda_swe]

Hi,

 

Mabey i dont understand, but wouldn't simply

 

ALTER IGNORE TABLE  tablenamne ADD UNIQUE INDEX(`user`);

 

delete all your user duplicates from your existing table and also add a unique index so you dont get the same problem in the future 

 

Hmm..you might want to try it on a fake table before running the query on the real one.

[Kryptix]

Hi,

 

Mabey i dont understand, but wouldn't simply

 

ALTER IGNORE TABLE  tablenamne ADD UNIQUE INDEX(`user`);

 

delete all your user duplicates from your existing table and also add a unique index so you dont get the same problem in the future 

 

Hmm..you might want to try it on a fake table before running the query on the real one.

I think that worked, but is there anyway to tell exactly what it deleted? It didn't give the result I expected, but of course, my original guess may have been off. I'm still a little scared to continue without knowing what happened though.

[roopurt18]

If DavidAM's solution copied "duplicate" records over, then the records were not duplicates in the truest of senses.  In other words, values in the other columns were different.

[benphp]

Using PHP, where pid is your unique identifier:

 

<?php
$selectmain = "select pid, count(pid) as idc from yourtable group by pid order by idc desc";
//define MAIN SQL 

//run SQL and get results
$result = mysql_query($selectmain) or trigger_error("SQL", E_USER_ERROR);
print "<h2>List of duplicate PIDs</h2><table border=1 bgcolor=ffffff>";
while($row = mysql_fetch_row($result))	//set $row to a row array from the query results
{
$pid	= $row[0];	//assign a var to each element in the array
$pcount = $row[1];

if ($pcount > 1) {
print "<tr><td>$pid</td><td>$pcount</td></tr>";
}

} //END MAIN SQL
print "</table>";
?>

 

Instead of printing, you could run a DELETE loop.

[fenway]

See here.