Jump to content

Deleting Duplicated Entries


Kryptix

Recommended Posts

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?

Link to comment
Share on other sites

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. :-\

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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  :confused:

 

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

Link to comment
Share on other sites

  • 3 weeks later...

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  :confused:

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.