MySQL |您无法在FROM子句中指定目标表"a"进行更新 [英] MySQL | You can't specify target table 'a' for update in FROM clause

问题描述

DELETE FROM table_a WHERE id IN(
    SELECT table_a.id AS id FROM table_a, table_b 
    WHERE table_a.object_id = 1 AND table_a.code = 'code' 
        AND table_a.code = table_b.code 
        AND table_b.id = table_a.b_id 
        AND table_b.table = 'testTable')

这是我希望MySQL执行的(某种程度上简化了的)查询.我在stackoverflow的其他页面上读到，不支持此功能，并且可以通过使用JOINS来解决.如何使用JOINS将其转录"为查询?我发现很难做到这一点，因为我从未尝试使用多个表创建DELETE查询.

This is a (somewhat simplified) query I want MySQL to execute. I read on other pages of stackoverflow that this wasn't supported and that it's solvable by using JOINS. How could this be 'transcribed' to a query using JOINS? I find it hard to do so, because I've never tried creating DELETE queries with more than one table.

推荐答案

您不能从表中删除并在子查询中引用同一表—只是MySQL的限制.像下面这样的东西应该起作用:

You can't delete from a table and reference the same table in a subquery — just a limitation of MySQL. Something like the following should work:

DELETE FROM table_a 
USING table_a
INNER JOIN table_b
    ON table_a.code = table_b.code
    AND table_b.id = table_a.b_id
    AND table_b.table = 'testTable'
WHERE table_a.object_id = 1 
    AND table_a.code = 'code' 

重要的部分是USING.如果仅将两个表连接在一起，则将从两个表中删除记录. USING告诉MySQL使用这些表进行处理，但只能从FROM子句中的表中删除.

The important part is USING. If you just join the two tables, you'll delete records from both. USING tells MySQL to use these tables for processing, but only delete from the tables in the FROM clause.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

这篇关于MySQL |您无法在FROM子句中指定目标表"a"进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！