[mysql] MySQL LIMIT on DELETE statement

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?

This question is related to mysql limit sql-delete

The answer is


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.


From the documentation:

You cannot use ORDER BY or LIMIT in a multiple-table DELETE.

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 > Dup.id 
 AND tableX .email= Dup.email 
 AND tableX .mobil = Dup.mobil

This worked.


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

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.


simply use

DELETE FROM test WHERE 1= 1 LIMIT 10 

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to limit

Laravel Eloquent limit and offset How to increase Neo4j's maximum file open limit (ulimit) in Ubuntu? Pagination using MySQL LIMIT, OFFSET How to fix the "508 Resource Limit is reached" error in WordPress? How to limit the number of dropzone.js files uploaded? Google drive limit number of download How can I list (ls) the 5 last modified files in a directory? Limiting Python input strings to certain characters and lengths MySQL JOIN with LIMIT 1 on joined table MySQL - UPDATE query with LIMIT

Examples related to sql-delete

Delete all rows with timestamp older than x days How to delete duplicate rows in SQL Server? How to delete and update a record in Hive How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? How can I delete using INNER JOIN with SQL Server? How to delete multiple rows in SQL where id = (x to y) Delete many rows from a table using id in Mysql How do I delete all the duplicate records in a MySQL table without temp tables Delete with "Join" in Oracle sql Query MySQL WHERE: how to write "!=" or "not equals"?