DELETE + JOIN + ORDER BY + LIMIT =语法错误 [英] DELETE + JOIN + ORDER BY + LIMIT = syntax error

问题描述

拖放ORDER BY + LIMIT，或 JOIN，一切都是桃子.把它们放在一起，我似乎释放了《海妖》.谁能阐明一些想法?

Drop the ORDER BY + LIMIT, or the JOIN, and everything is peaches. Put them together and I seem to release the Kraken. Anyone who can shed some light?

DELETE table1 AS t1
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id = t2.id
WHERE t2.field = 'something'
ORDER BY t1.id DESC
LIMIT 5

(我也尝试了不带别名&的尝试.放弃WHERE，无济于事.始终是语法错误"near 'ORDER BY...".

I've also tried it without aliases & dropping the WHERE, to no avail. Always a syntax error "near 'ORDER BY...".

推荐答案

来自Mysql Docs: DELETE

From Mysql Docs: DELETE

对于多表语法，DELETE从每个tbl_name中删除满足条件的行. 在这种情况下，不能使用ORDER BY和LIMIT .

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

对于您而言，我认为这可行:

In your case, I think this works:

DELETE 
FROM table1
WHERE EXISTS
      ( SELECT t2.id
        FROM table2 AS t2
        WHERE t2.id = table1.id
          AND t2.field = 'something'
      ) 
ORDER BY id DESC
LIMIT 5

这篇关于DELETE + JOIN + ORDER BY + LIMIT =语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！