Delete duplicate records from table where id is duplicate

Question

I have a little problem, my table 'furniture' contains records where the ID is the same. I'll show you.

Title

id : public_name
1 : 'desk'
1 : 'desk'
1 : 'desk'
2 : 'comp'
2 : 'comp'
3 : 'rug'
3 : 'rug'
3 : 'rug'
3 : 'rug'

I don't know how it happened, but the record for id 2 has been duplicated, and some of them has even been tripled. I've tried this script to solve it.

<?php

$con = mysql_connect("localhost","root","pass");
Mysql_Select_db("db", $con);


for($i = 0; $i < 52752; $i++) {
$find = mysql_query("SELECT * FROM furniture WHERE id = '".$i."'");
$count = mysql_num_rows($find);

mysql_query("DELETE FROM furniture WHERE id = '$i' LIMIT ($i - $count)");
echo "'.$i.' fixed";
}

But it is not working, it deletes all off the ids, I want it to leave one record per id.

Is there any easy query or script to solve this?

https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — LiverpoolOwen, Dec 09 '17 at 14:35
In your attempt, this bit: `($i - $count)` is why its deleting them all. You are taking a big index, and only removing 2 or 3 from it. So an index of 382 - 2 is way more than it had. So it deletes them all. I think what you wanted there was `($count - 1)`. Also you would only want to execute the delete `if ($count > 1)` ... ;) — IncredibleHat, Dec 09 '17 at 14:49

score 3 · Answer 1 · answered Dec 09 '17 at 14:33

The simplest method in MySQL is to empty and re-populate the table:

create table temp_furniture as
    select distinct id, public_name
    from furniture;

truncate table temp_furniture;

insert into temp_furniture(id, public_name)
    select id, public_name
    from temp_furniture;

Then, to prevent this happening in the future, add unique indexes/constraints:

create unique index unq_furniture_id on furniture(id);

And you probably want on on the name as well:

create unique index unq_furniture_public_name on furniture(public_name);

I don't exactly understand, if it's to any help. My furniture table structure is like this https://pastebin.com/CLwC29pL — Karl Emrik, Dec 09 '17 at 14:39

Delete duplicate records from table where id is duplicate

1 Answers1