使用 INNER JOIN 更新 SQL Server 中的多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15115759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple tables in SQL Server using INNER JOIN
提问by Coyolero
I'm using SQL Server and trying to use SQL to update multiple tables at once with one query:
我正在使用 SQL Server 并尝试使用 SQL 通过一个查询一次更新多个表：
The following query:
以下查询：
update table1
set A.ORG_NAME = @ORG_NAME, B.REF_NAME = @REF_NAME
from table1 A, table2 B
where B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID
Gives the error message:
给出错误信息：
The multi-part identifier "A.ORG_NAME" could not be bound.
无法绑定多部分标识符“A.ORG_NAME”。
What does the error message mean?
错误信息是什么意思？
回答by jazzytomato
You can't update more that one table in a single statement, however the error message you get is because of the aliases, you could try this :
您不能在单个语句中更新多个表，但是您收到的错误消息是由于别名，您可以尝试以下操作：
BEGIN TRANSACTION
update A
set A.ORG_NAME = @ORG_NAME
from table1 A inner join table2 B
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID
update B
set B.REF_NAME = @REF_NAME
from table2 B inner join table1 A
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID
COMMIT
回答by Eric Leschinski
You can update with a join if you only affect one table like this:
如果您只影响这样的一张表，则可以使用连接进行更新：
UPDATE table1
SET table1.name = table2.name
FROM table1, table2
WHERE table1.id = table2.id
AND table2.foobar ='stuff'
But you are trying to affect multiple tables with an update statement that joins on multiple tables. That is not possible.
但是您正在尝试使用连接多个表的更新语句来影响多个表。这是不可能的。
However, updating two tables in one statement is actually possible but will need to create a View using a UNION that contains both the tables you want to update. You can then update the View which will then update the underlying tables.
但是，在一个语句中更新两个表实际上是可能的，但需要使用包含要更新的两个表的 UNION 创建一个视图。然后，您可以更新视图，然后该视图将更新基础表。
But this is a really hacky parlor trick, use the transaction and multiple updates, it's much more intuitive.
但这是一个非常hacky的客厅技巧，使用事务和多次更新，它更直观。
