CODEWITHSUNDEEP
mysqlsql
Monagraphic
Monagraphic

Reputation: 102

Removing duplicate rows in MySQL

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.

Upvotes: 1

Views: 792

Answers (5)

Monagraphic
Monagraphic

Reputation: 102

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.

Upvotes: 0

Pedro del Sol
Pedro del Sol

Reputation: 2841

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.

Upvotes: 2

MohanaPriyan
MohanaPriyan

Reputation: 218

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

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

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
    )

Upvotes: 5

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

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;

Upvotes: 2

Related Questions