Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple tables in SQL Server using INNER JOIN [duplicate]

I'm using SQL Server and trying to use SQL to update multiple tables at once with one query:

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.

What does the error message mean?

like image 394
Coyolero Avatar asked Feb 27 '13 15:02

Coyolero


People also ask

How does inner join work with duplicates?

if join two tables using inner join method will it return duplicate values ? The answer is yes, if there are any. If there are duplicate keys in the tables being joined.

Is it possible to update two tables using join in a single update statement?

In SQL, there is a requirement of a single query/statement to simultaneously perform 2 tasks at the same time. For instance, updating 2 different tables together in a single query/statement. This involves the use of the BEGIN TRANSACTION clause and the COMMIT clause.

How can I update multiple tables in a single query in SQL Server?

You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO , and you can use this output as a join for the second update: DECLARE @ids TABLE (id int); BEGIN TRANSACTION UPDATE Table1 SET Table1. LastName = 'DR.


2 Answers

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 
like image 136
jazzytomato Avatar answered Sep 19 '22 19:09

jazzytomato


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.

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

like image 21
Eric Leschinski Avatar answered Sep 18 '22 19:09

Eric Leschinski