Deleting many rows from a big table MySql 5.5.46

Edward Sheriff Curtis

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.

nbk

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

Collected from the Internet

Please contact debugcn@gmail.com to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Deleting rows from 3 tables in MySQL table

From Dev

Efficiently deleting rows from one table where not matching another [MySQL]

From Dev

MySQL: deleting rows based on a condition with data from another table and NO JOIN

From Dev

Deleting multiple rows from a table

From Dev

Deleting multiple rows from a table

From Dev

Creating 5 reports from top 5 rows of filtered table

From Dev

Loading 5 million rows into Pandas from MySQL

From Dev

Laravel 5 Deleting a one-to-many relationship

From Dev

mysql table not decreased in size after deleting rows

From Dev

Adding or deleting table rows with MySQL, jQuery and Ajax

From Dev

Script for deleting table rows not working in MySQL & PHP

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

PostgreSQL: Obtain rows from table from 5 minutes ago

From Java

Deleting all rows from Cassandra cql table

From Dev

PostgreSQL: deleting rows referenced from another table

From Dev

Issue deleting rows from displayed table

From Dev

Optimal MYSQL query for longest prefix matching in a table with 5 million rows

From Dev

Optimal MYSQL query for longest prefix matching in a table with 5 million rows

From Dev

mysql - Deleting Rows from InnoDB is very slow

From Dev

mysql - Deleting Rows from InnoDB is very slow

From Dev

Filling a Big Table Rows from JSON Data

From Dev

Selecting the top 5 rows from a joined table, into the result of a larger query?

From Dev

Selecting the top 5 rows from a joined table, into the result of a larger query?

From Dev

Laravel 5 Many to Many - Table name in singular

From Dev

MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

From Dev

Deleting Row from users table PHP MYSQL

From Dev

AJAX/Jquery not deleting row from MySQL table

From Dev

PHP Mysql always NEED to return 5 rows even 3rows present in table

Related Related

  1. 1

    Deleting rows from 3 tables in MySQL table

  2. 2

    Efficiently deleting rows from one table where not matching another [MySQL]

  3. 3

    MySQL: deleting rows based on a condition with data from another table and NO JOIN

  4. 4

    Deleting multiple rows from a table

  5. 5

    Deleting multiple rows from a table

  6. 6

    Creating 5 reports from top 5 rows of filtered table

  7. 7

    Loading 5 million rows into Pandas from MySQL

  8. 8

    Laravel 5 Deleting a one-to-many relationship

  9. 9

    mysql table not decreased in size after deleting rows

  10. 10

    Adding or deleting table rows with MySQL, jQuery and Ajax

  11. 11

    Script for deleting table rows not working in MySQL & PHP

  12. 12

    Remove duplicate rows on many to many table (Mysql)

  13. 13

    Remove duplicate rows on many to many table (Mysql)

  14. 14

    PostgreSQL: Obtain rows from table from 5 minutes ago

  15. 15

    Deleting all rows from Cassandra cql table

  16. 16

    PostgreSQL: deleting rows referenced from another table

  17. 17

    Issue deleting rows from displayed table

  18. 18

    Optimal MYSQL query for longest prefix matching in a table with 5 million rows

  19. 19

    Optimal MYSQL query for longest prefix matching in a table with 5 million rows

  20. 20

    mysql - Deleting Rows from InnoDB is very slow

  21. 21

    mysql - Deleting Rows from InnoDB is very slow

  22. 22

    Filling a Big Table Rows from JSON Data

  23. 23

    Selecting the top 5 rows from a joined table, into the result of a larger query?

  24. 24

    Selecting the top 5 rows from a joined table, into the result of a larger query?

  25. 25

    Laravel 5 Many to Many - Table name in singular

  26. 26

    MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

  27. 27

    Deleting Row from users table PHP MYSQL

  28. 28

    AJAX/Jquery not deleting row from MySQL table

  29. 29

    PHP Mysql always NEED to return 5 rows even 3rows present in table

HotTag

Archive