主页 / dba / 问题 / 33406
Accepted
Pietro
Asked: 2013-01-24 06:08:52 +0800 CST

嵌套更新查询的问题

  • 772

我无法进行更新查询。作为第一步，以下步骤可以正常工作： 

UPDATE Tab1  
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

其中 id 是 Tab1 的主键。
但是，当我添加一个选择以获取 Tab1.id 必须包含的一组值时，我收到以下 MySQL 错误：

错误代码：1093
您不能在 FROM 子句中指定目标表“Tab1”进行更新。

这是完整的查询： 

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

我看不到如何解决此错误。
我是否被迫将此查询一分为二？

谢谢！

mysql update

3 个回答

  1. Best Answer

    尝试

    UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

    查看文档以获取更多信息（查找“多表语法”）。重要的一点是：

    如果您使用涉及具有外键约束的 InnoDB 表的多表 UPDATE 语句，则 MySQL 优化器可能会以不同于其父/子关系的顺序处理表。在这种情况下，语句失败并回滚。相反，更新单个表并依赖 InnoDB 提供的 ON UPDATE 功能来相应地修改其他表。请参见第 14.3.5.4 节，“外键约束”。

    您可以在SQLFiddle上查看类似的示例。

    • 5

  2. 补充资料

    使用子查询指定更新可能是危险的事情。

    早在 2011 年 2 月 21 日，有人问了这个问题：MySQL 子查询问题。

    我做了一些研究，发现了一些令人不安的事情：在优化涉及针对自身的子查询的查询期间，行完全有可能间歇性消失。

    因此，请考虑错误信息

    错误代码：1093
    您不能在 FROM 子句中指定目标表“Tab1”进行更新。

    来自 MySQL的友好警告，不要用这种风格编写查询

    UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

    @dezso 的答案（得到 +1），它使用了 anUPDATE JOIN，对 MySQL 查询优化器来说更可口

    • 2

  3. 在这种情况下，@dezso 的答案是正确的选择；但是，当面临引用同一个表的更新时，有时还有另一种方法会派上用场。

    当您需要根据要从表本身派生的内容来更新表时，您可以非常一致地说服优化器在处理外部查询之前将不相关的子查询具体化为隐式临时表。

    UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT * FROM (                            /* add this line */ 
                SELECT Tab1.id 
                FROM Tab1, Tab2 
                WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
            ) hack                                     /* and this line */
        );

    hack该临时表的别名在哪里。那里没有某种名称（以及AS名称前的可选名称）：

    ERROR 1248 (42000): Every derived table must have its own alias

    这实际上是记录在案的行为，并且在没有更合适的解决方案（例如联接）时会派上用场。

    这里 FROM 子句中子查询的结果存储为临时表，因此在更新发生t时已经选择了其中的相关行。t

    http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html

    • 1

相关问题