多表删除限制 [英] Multiple-table DELETE LIMIT

问题描述

我正在尝试运行以下查询，但是会引发错误.

I'm trying to run following query but it throws an error.

DELETE b 
FROM parim_lang a 
JOIN parim_lang b 
    ON b.lang_hash = a.lang_hash 
    AND b.lang_language = SUBSTRING(a.lang_google_translation, LOCATE('-', a.lang_google_translation) + 1)
WHERE a.lang_google_translation REGEXP '^[a-z]+-[a-z]+'
LIMIT 20

此查询无限制..

错误看起来像这样:

SQL错误(1064):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第7行的"LIMIT 20"附近使用

SQL Error (1064): 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 20' at line 7

发现了这个

对于多表语法，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.

我对此有任何解决方法吗?

I there any workarounds for this?

推荐答案

尝试:

DELETE
FROM parim_lang a
WHERE a.lang_hash IN (
SELECT a.lang_hash 
FROM parim_lang a
JOIN parim_lang b 
    ON b.lang_hash = a.lang_hash 
    AND b.lang_language = SUBSTRING(a.lang_google_translation, LOCATE('-', a.lang_google_translation) + 1)
WHERE a.lang_google_translation REGEXP '^[a-z]+-[a-z]+' ) LIMIT 20

这篇关于多表删除限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！