使用INNER JOIN更新SQL Server中的多个表[重复]

This question already has an answer here:

这个问题在这里已有答案：

How to update two tables in one statement in SQL Server 2005? 8 answers
如何在SQL Server 2005中的一个语句中更新两个表？ 8个答案

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?

错误消息是什么意思？

2 个解决方案

#1

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

#2

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创建View。然后，您可以更新View，然后更新基础表。

SQL JOINS

But this is a really hacky parlor trick, use the transaction and multiple updates, it's much more intuitive.

但这是一个真正的hacky客厅技巧，使用事务和多个更新，它更直观。

#1