Deleting many rows from a big table MySql 5.5.46

Question

The following statement deletes duplicate rows and keeps the highest id

DELETE t1 FROM contacts t1
INNER JOIN contacts t2 
WHERE 
    t1.id < t2.id AND 
    t1.email = t2.email;

This query references the contacts table twice, therefore, it uses the table alias t1 and t2.

But I have a 6 millions rows table and we need to clean it.

My first approach was create a SP with this lines

REPEAT
DELETE t1 FROM contacts t1
INNER JOIN contacts t2 
WHERE 
    t1.id < t2.id AND 
    t1.email = t2.email
ORDER BY t1.id ASC LIMIT 10000;
UNTIL ROW_COUNT() = 0 END REPEAT;

The error is

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 'ORDER BY t1.id ASC LIMIT 10000' at line 17 Time: 0,063s

Help me to do it.

Answer 1

You can't use ORDER and LIMIT in a joined tables in DELETE

Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

See manual