how to delete row in mysql when duplicated with other row?

Question

how to delete row in mysql when duplicated with other row ?

i want to create page delete_duplicate.php

when i load pagr delete_duplicate.php

it's will be delete row id 3 and 4

because row id 3 duplicate row id 2 and row id 4 duplicate row id 1

How to write that code [php] ?

Before load page delete_duplicate.php------table : member

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____3_____|__100005__|____bbbbbb____|
|____4_____|__100261__|____aaaaaa____|
|____5_____|__100261__|____cccccc____|

After load page delete_duplicate.php------table : member

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____5_____|__100261__|____cccccc____|

Follow the link i think u can get answer http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Tehsil, Mar 12 '14 at 12:01

score 3 · Answer 1 · answered Mar 12 '14 at 12:00

You can fix this problem permanently by creating a unique index on user_id and product_id:

create unique index member_userid_productid on member(userid, productid);

For your example, you can delete the duplicates by using delete with join:

delete m from members m join
              (select m.user_id, m.product_id, min(m.like_id) as minlike_id
               from members m
               group by m.user_id, m.product_id
              ) tokeep
              on m.user_id = tokeep.user_id and
                 m.product_id = tokeep.product_id and
                 m.like_id > tokeep.minlike_id;

score 2 · Answer 2 · edited Mar 12 '14 at 12:40

2

You should issue the following statement in order to delete the duplicate records from your table and achieve uniqueness of product_id:

DELETE e1 FROM member e1, member e2 WHERE e1.product_id = e2.product_id AND e1.likeid > e2.likeid;

edited Mar 12 '14 at 12:40

Bolo

11,542
7
41
60

answered Mar 12 '14 at 12:27

Nayan Dabhi

986
9
20

score 1 · Answer 3 · answered Mar 12 '14 at 12:03

1

If you want to achieve it through SQL then below query can help

DELETE FROM member m1, member m2 WHERE m1.likeid > m2.likeid 
AND m1.userid = m2.userid 
AND m1.product_id = m2.product_id

answered Mar 12 '14 at 12:03

Rahul

76,197
13
71
125

Hovo · Answer 4 · 2014-03-12T12:54:50.020

1

You can delete all the duplicates by this mysql query, if you know how to execute it from the php.

DELETE FROM `member`
WHERE `member`.`likeid` NOT IN (
    SELECT `temp_member`.`min_likeid`
    FROM (
        SELECT MIN(`likeid`) AS `min_likeid`
        FROM `member`
        GROUP BY `member`.`userid`, `member`.`product_id`


    ) AS `temp_member`)

edited Mar 12 '14 at 12:54

answered Mar 12 '14 at 12:13

Hovo

790
4
21

score 0 · Answer 5 · answered Mar 12 '14 at 12:01

You can do the check before creating the database entry (best pratice):

Make a unique constraint of the combination userid/product_id.

You can delete duplicate entries after they are entered in the database:

Load your table into your php code with the SQL command SELECT DISTINCT
Empty your table
Write the php entries back to your table.

oBo · Answer 6 · 2014-03-12T12:22:18.747

EDIT: This only works if you have a primary key of "id", swap id to "likeid"

You could do it with php

something like

$config['db'] = array(
    'host'          => 'localhost',
    'username'      => 'root',
    'password'      => 'root',
    'dbname'        => 'dbname'
    );

$db = new PDO("mysql:host={$config['db']['host']};dbname={$config['db']['dbname']}",
              $config['db']['username'], $config['db']['password']);

$query = "SELECT * FROM member";

$stmt = $db->prepare($query);
$members = $stmt->fetch(PDO::FETCH_ASSOC);

Now you have all the members in $members , so you can loop over them

$idsToDelete= [];
$array = [];

foreach($members as $member)
{

    if(in_array($member['product_id'], $array)){
        $idsToDelete[] = $member['id'];
    }

    $array[] = $member['product_id'];
}

To remove the duplicates

$removeQuery  = "DELETE FROM from members WHERE id in (:idsToDelete)";
$stmt = $db->prepare($removeQuery);
$stmt->bindParam(':idsToDelete', $idsToDelete);
$stmt->execute();

how to delete row in mysql when duplicated with other row?

6 Answers6