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 Too中也可以！

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.

使用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屋！