MySQL 更新连接表

MySQL update a joined table

提问by Zugwalt

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;

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!

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

回答by Bill Karwin

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.

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

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;

There is no FROM clause in MySQL's syntax.

MySQL 的语法中没有 FROM 子句。

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.

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

回答by Andreas Wederbrand

You have the ordering of the statements wrong. You can read up on the syntax here(I know, it's pretty hard to read.

您对语句的顺序有误。您可以在此处阅读语法（我知道，这很难阅读。

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;

sql fiddle

sql 小提琴

回答by John Kane

This linkshould give you the syntax that MySQL needs and hereis an example. Why do you need to join the two tables? is it to limit the records updated? I am asking because you can also do something like the following:

此链接应为您提供 MySQL 所需的语法，这是一个示例。为什么需要join这两个表？是限制更新记录吗？我问是因为您还可以执行以下操作：

update B set B.x=<value>
    where 
B.<value> is in(
    select A.y 
      from A left outer join B on A.<value>=B.<value>
)

回答by simhumileco

Another correct construction, which we can use in this situation:

另一个正确的构造，我们可以在这种情况下使用：

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

The above example is take from: MySQL UPDATE JOIN.

上面的例子取自：MySQL UPDATE JOIN。

Reaching for the MySQL 8.0 Reference Manualwe will find such a description of multiple-table UPDATE syntax:

查阅MySQL 8.0 参考手册，我们会发现多表 UPDATE 语法的描述如下：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

The table_referencesclause lists the tables involved in the join.

该table_references部分列出了参与的表join。

So multiple-table MySQL's syntaxdoesn't support FROM, ORDER BYor LIMITclauses as opposed to single-table syntax.

所以多表 MySQL 的语法不支持FROM, ORDER BYorLIMIT子句而不是单表语法。