1

I have created query to delete rows from million rows table in my sql. the query is given below,

DELETE N
FROM table_a N 
INNER JOIN table_b E ON N.form_id = E.form_id 
                     AND N.element_id = E.element_id 
                     AND E.element_type IN('checkbox','radio','select')
WHERE N.option_value = 0
ORDER BY id
LIMIT 1000;

When executing the above query. gets error.

ERROR 1064 (42000): 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 id
LIMIT 1000' at line 7

How to fix this issue. I have used the reference Deleting millions of rows in MySQL

Noufal netspective
  • 101
  • 1
  • 1
  • 8
  • note only the [single table DELETE syntax](https://dev.mysql.com/doc/refman/5.7/en/delete.html) has `ORDER BY` and `LIMIT` – danblack Sep 24 '21 at 04:27

1 Answers1

0

MySQL's multi-table DELETE syntax doesn't allow LIMIT. But you can use more conditions in the WHERE clause:

DELETE N
FROM table_a N 
INNER JOIN table_b E ON N.form_id = E.form_id 
                     AND N.element_id = E.element_id 
                     AND E.element_type IN('checkbox','radio','select')
WHERE N.option_value = 0
  AND N.id BETWEEN 1 AND 1000;

This might of course delete fewer than 1000, if there are gaps such that not all values of N.id are used. But at least it won't delete more than 1000 rows (assuming id is a unique key).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the reply. i have tried with the above query. but getting error. error=> 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 N.id' at line 8 – Noufal netspective Sep 24 '21 at 05:31
  • Then remove the ORDER BY clause. It also must not be allowed when using multi-table DELETE. If you're specifying a range of `id` then it isn't needed anyway. I'll edit my answer above and remove the ORDER BY clause. – Bill Karwin Sep 24 '21 at 21:27
  • Thank you .................. . – Noufal netspective Sep 26 '21 at 04:19