mysql - 无法在FROM子句中为UPDATE指定目标表

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

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。
table3是table1和table2之间的联接表。

最佳答案

将其包装在子查询中（从而为结果创建临时表）。我也建议使用ANSI SQL-92格式。

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'

关于mysql - 无法在FROM子句中为UPDATE指定目标表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13188603/