By Andre


2011-05-24 14:25:28 8 Comments

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?

6 comments

@Michel de Ruiter 2019-03-21 09:51:53

From the documentation:

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

@davidman77 2018-10-24 08:16:49

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.

@Peter 2019-01-22 07:22:33

I downvoted your answer, because it's not an answer on the actual topic. Please see the question above.

@ThelmaJay 2015-10-20 13:44:02

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.

@Nicolas Thery 2018-06-29 13:58:15

doesn't solve for me

@Manoj Gupta 2014-01-16 06:31:11

simply use

DELETE FROM test WHERE 1= 1 LIMIT 10 

@Fabien TheSolution 2015-03-05 15:59:43

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"

@Nicolas Thery 2018-06-29 13:58:51

I can't use delete from because I need to join data

@Reign.85 2019-01-28 10:09:47

why do you use where 1 = 1 ?

@Joshua joseph bissot 2015-08-04 21:21:53

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

@Ian Wood 2011-05-24 14:29:37

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

see this page

@Andre 2011-05-24 15:06:13

"However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE". This seems to answer my question.

@Ian Wood 2011-05-24 15:17:03

yes - sorry didn't read that from the OP but what you say about deleting from a join is correct

Related Questions

Sponsored Content

43 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

34 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 537959 View
  • 227 Score
  • 2 Answer
  • Tags:   mysql sql

6 Answered Questions

[SOLVED] How to delete from multiple tables in MySQL?

1 Answered Questions

[SOLVED] Mysql delete statement with limit

1 Answered Questions

[SOLVED] How to set correct limit for mysql delete statement

Sponsored Content