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