75

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?

Improve this question

6 Answers 6

Reset to default
43

simply use 

DELETE FROM test WHERE 1= 1 LIMIT 10
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The OP clearly stated that he knows that this is working..."I'm fully aware that I can use another statement to accomplish this DELETE successfully.[...] DELETE FROM test WHERE name = 'foo' LIMIT 1"
29

the delete query only allows for modifiers after the DELETE 'command' to tell the database what/how do handle things.

see this page

Improve this answer

2 Comments

"However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE". This seems to answer my question.
yes - sorry didn't read that from the OP but what you say about deleting from a join is correct
18

From the documentation:

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

Improve this answer

Comments

6 
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.

Improve this answer

Comments

4

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.

Improve this answer

1 Comment

For a more complex query joining multiple tables, you need to repeat that complex query for the derived table, or else (in your example) the first id from test might not be in the delete condition, so no records will be deleted.
2

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.