MySQL LIMIT on DELETE statement

Question

I put together a test table for a error I recently came across  It involves the use of LIMIT when attempting to delete a single record from a MySQL table   The error I speak of is  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  LIMIT 1  at line 1   The table I put together is called test  it has 3 columns  id  name and created  I populated the table with several records and then attempted to delete one  Below is the statement I used to try and accomplish this   DELETE t FROM test t WHERE t name    foo  LIMIT 1  Without the use of LIMIT 1  the statement executes just fine  but of course I wouldn t be using LIMIT if there wasn t a need for it   I m fully aware that I can use another statement to accomplish this DELETE successfully  See below  DELETE FROM test WHERE name    foo  LIMIT 1  However my question is centered on why the first statement isn t working with LIMIT   So my question is  what I have done incorrectly with respect to the first statement to generate this error

User · Answer

From the documentation   You cannot use ORDER BY or LIMIT in a multiple-table DELETE

User · Answer

the delete query only allows for modifiers  after the DELETE  command  to tell the database what how do handle things   see this page

User · Answer

First I struggled a bit with a   DELETE FROM     USING     WHERE query      Since i wanted to test first so i tried with SELECT FROM     USING    WHERE      and this caused an error        Then i wanted to reduce the number of deletions adding LIMIT 10  which also produced an error Then i removed the  LIMIT  and - hurray - it worked   1867 rows deleted   Query took 1 3025 seconds     The query was    DELETE FROM tableX  USING tableX   tableX as Dup  WHERE NOT tableX  id   Dup id   AND tableX  id  gt  Dup id   AND tableX  email  Dup email   AND tableX  mobil   Dup mobil   This worked

User · Answer

Use row count - your desired offset   So if we had 10 rows and want to offset 3   10 - 3   7   Now the query delete from table where this   that order asc limit 7 keeps the last 3  and  order desc to keep the first 3    row count -  offset    limit  Delete from table where entry   criteria order by ts asc limit  limit

User · Answer

There is a workaround to solve this problem by using a derived table  DELETE t1 FROM test t1 JOIN  SELECT t id FROM test LIMIT 1  t2 ON t1 id   t2 id  Because the LIMIT is inside the derived table the join will match only 1 row and thus the query will delete only this row

User · Answer

DELETE t   FROM test t WHERE t name    foo  LIMIT 1    Andre If I understood what you are asking  I think the only thing missing is the t   before FROM

User · Answer

simply use   DELETE FROM test WHERE 1  1 LIMIT 10

[mysql] MySQL LIMIT on DELETE statement

Examples related to mysql

Examples related to limit

Examples related to sql-delete