不能在 FROM 子句中为 UPDATE 指定目标表 [英] can't specify target table for UPDATE in FROM clause

问题描述

我正在尝试执行以下查询:

I am trying to execute the following query:

update table3 d set status = 'Complete'
where d.id in 
(
    select b.id from table1 a, table3 b, table2 c
    where a.id = b.table1_id
    and c.id = b.table2_id
    and c.examId = 16637                 -- will be passed in by user
    and a.id in (46,47,48,49)            -- will be passed in by user
);

所以，我正在尝试更新 table3 的多行.

So, I'm trying to update multiple rows of table3.

table3 是 table1 和 table2 之间的连接表.

table3 is a join table between table1 and table2.

推荐答案

将其包装在子查询中，(从而为结果创建一个临时表).我还建议使用 ANSI SQL-92 格式.

wrap it in a subquery, (thus creating a temporary table for the result). I'm also recommending to use ANSI SQL-92 format.

update table3 d 
set    status = 'Complete'
where  d.id in 
(
    SELECT ID
    FROM
    (
        select  b.id 
        from    table1 a 
                INNER JOIN table3 b
                    ON a.id = b.table1_id
                INNER JOIN table2 c
                    ON c.id = b.table2_id
        where   c.examId = 16637 and 
                a.id in (46,47,48,49) 
    ) xx
);

或使用 JOIN

update  table3 d 
        INNER JOIN
        (
            SELECT ID
            FROM
            (
                select  b.id 
                from    table1 a 
                        INNER JOIN table3 b
                            ON a.id = b.table1_id
                        INNER JOIN table2 c
                            ON c.id = b.table2_id
                where   c.examId = 16637 and 
                        a.id in (46,47,48,49) 
            ) xx
        ) y ON d.id = y.id
set status = 'Complete'

这篇关于不能在 FROM 子句中为 UPDATE 指定目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！