Bug #544 LIMIT not accepted with multi-table DELETE
Submitted: 30 May 2003 0:25 Modified: 30 May 2003 4:23
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 OS:Any (All)
Assigned to: CPU Architecture:Any

[30 May 2003 0:25] Jeremy Cole
Description:
LIMIT clause is a syntax error when using multi-table DELETE

How to repeat:
mysql> delete from a left join b on a.i=b.i limit 1;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'left join b
on a.i=b.i limit 1' at line 1

Suggested fix:
Fix the parser. :)
[30 May 2003 0:27] Jeremy Cole
Oopsie, wrong query:

mysql> delete a from a left join b on a.i=b.i limit 1;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'limit 1' at
line 1
[30 May 2003 4:23] MySQL Verification Team
ORDER BY .. LIMIT ... will not be introduced in either multi-table updates nor deletes for many reasons, of which the most important one is the ambiguity of number of tuples and great probability of breaking entity integrity with non-transactional tables.