MySQL 更新连接表 [英] MySQL update a joined table

问题描述

我想更新具有多个连接的语句中的表.虽然我知道连接的顺序并不重要(除非您使用优化器提示)，但我以某种方式对它们进行了排序，以便最直观地阅读.但是，这导致我想要更新的表不是我开始使用的表，并且我无法更新它.

I want to update a table in a statement that has several joins. While I know the order of joins doesn't really matter (unless you you are using optimizer hints) I ordered them a certain way to be most intuitive to read. However, this results in the table I want to update not being the one I start with, and I am having trouble updating it.

我想做的一个虚拟示例如下:

A dummy example of what I'd like to do is something like:

UPDATE b
FROM tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
    AND c.val > 10;

这里有很多关于使用连接更新的帖子，但是他们总是先更新表.我知道这在 SQL Server 中是可能的，希望它在 MySQL 中也是可能的！

There are many posts about updating with joins here however they always have table being updated first. I know this is possible in SQL Server and hopefully its possible in MySQL Too!

推荐答案

MySQL 中的多表 UPDATE 语法与 Microsoft SQL Server 不同.您无需说明要更新哪个表，这在您的 SET 子句中是隐含的.

The multi-table UPDATE syntax in MySQL is different from Microsoft SQL Server. You don't need to say which table(s) you're updating, that's implicit in your SET clause.

UPDATE tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
    AND c.val > 10;

MySQL 的语法中没有 FROM 子句.

There is no FROM clause in MySQL's syntax.

UPDATE with JOIN 不是标准的 SQL，MySQL 和 Microsoft SQL Server 都实现了自己的想法，作为对标准语法的扩展.

UPDATE with JOIN is not standard SQL, and both MySQL and Microsoft SQL Server have implemented their own ideas as an extension to standard syntax.

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