Removing duplicate rows in MySQL

Question

In my database I have ~67 000 rows but i have 12k duplicate rows with different key.

I would like to remove duplicates and save only one row.

Table name: seb_societe

Similar columns: nom, phone, adress, zip

I tried this, but it did not work:

DELETE
FROM seb_societe AS T
WHERE  T.rowid < ANY (SELECT rowid
                FROM seb_societe AS T2
                WHERE  T.rowid <> T2.rowid
                  AND  T.nom = T2.nom
                  AND  T.address = T2.address);

Can anyone advise why my attempt didn't work, or how to achieve my desired outcome.

Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — simhumileco, May 24 '19 at 10:11

JohnHC · Answer 1 · 2017-04-07T14:06:49.420

5

delete
from seb_societe
where exists
    (
    select *
    from
        (
        select 1
        from seb_societe t2
        where t2.row_id > row_id
        and nom = T2.nom
        and address = T2.address
        ) x1
    )

edited Apr 07 '17 at 14:06

answered Apr 07 '17 at 13:56

JohnHC

10,935
1
24
40

Not works (not "row_id" but "rowid") : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 where exists ( select 1 from seb_societe t2 where t2.row' at line 2 – Monagraphic Apr 07 '17 at 13:58
Remove the t1 alias everywhere. – Guillaume CR Apr 07 '17 at 14:00
Here is my error : #1093 - You can't specify target table 'seb_societe' for update in FROM clause – Monagraphic Apr 07 '17 at 14:03
Edit made, MySQL handles the subquery while deleting/updating, so it needs wrapping – JohnHC Apr 07 '17 at 14:07
0 line deleted with this – Monagraphic Apr 07 '17 at 14:14

score 2 · Answer 2 · answered Apr 07 '17 at 14:01

2

You can use delete on join:

delete s
from seb_societe s
join (
    select nom,
        address,
        max(rowid) as max_rowid
    from seb_societe
    group by nom,
        address
    ) t on s.nom = t.nom
    and s.address = t.address
    and s.rowid <> t.max_rowid;

answered Apr 07 '17 at 14:01

Gurwinder Singh

38,557
6
51
76

Work but ... Any idea ? #2013 - Lost connection to MySQL server during query – Monagraphic Apr 07 '17 at 14:04
Not sure abt that – Gurwinder Singh Apr 07 '17 at 14:14

Pedro del Sol · Answer 3 · 2017-04-07T14:17:09.970

2

DELETE t1 FROM 
    seb_societe t1 LEFT JOIN seb_societe t2 ON 
    t1.nom = t2.nom AND t1.address = t2.address
WHERE t1.row_id < t2.row_id;

try on a back up first...

this should leave the row with the highest row_id of the duplicates.

edited Apr 07 '17 at 14:17

answered Apr 07 '17 at 14:04

Pedro del Sol

2,840
9
39
52

Work but ... Any idea ? #2013 - Lost connection to MySQL server during query – Monagraphic Apr 07 '17 at 14:06
Ewwww, old style joins – JohnHC Apr 07 '17 at 14:08
Wuuuuuuuuuuut ? – Monagraphic Apr 07 '17 at 14:15

score 1 · Answer 4 · answered Apr 07 '17 at 14:15

1

DELETE T FROM seb_societe T,seb_societe T2 WHERE T.rowid < T2.rowid AND T.nom = T2.nom AND T.address = T2.address;

answered Apr 07 '17 at 14:15

MohanaPriyan

218
3
9

Same problem ... #2013 - Lost connection to MySQL server during query – Monagraphic Apr 07 '17 at 14:17
problem is not with the query.Its is with your data. – MohanaPriyan Apr 07 '17 at 14:22
i think one of the rows is corrupted,try select * from seb_societe by ranging different you will get the corrupted row – MohanaPriyan Apr 07 '17 at 14:23
By ranging ? i have 67 000 lines xD – Monagraphic Apr 07 '17 at 14:29
then use binary search like,select * from seb_societe limit 67000/2,then – MohanaPriyan Apr 07 '17 at 14:31
select * from seb_societe limit 67000/2,67000 – MohanaPriyan Apr 07 '17 at 14:31
Yes but how can i find this line ? – Monagraphic Apr 07 '17 at 17:13

score 0 · Answer 5 · answered Apr 10 '17 at 10:39

Problem solved. I use a PHP script to delete duplicates lines :

global $dbh;
$query='SELECT rowid, nom FROM seb_societe GROUP BY `nom`, `address` HAVING COUNT(*) > 1';
$query = $dbh->query($query);
$i = 0;

$retour=array();

while($resultat = $query->fetch()) {
    $rowid = $resultat['rowid'];
    $suppression = $dbh->exec('DELETE FROM seb_societe WHERE rowid = '.$rowid); 
}

Before, i delete all lines in other tables with foreign keys.

Thanks all for your replies.

Removing duplicate rows in MySQL

5 Answers5