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?
This question is related to
sql
sql-server
tsql
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 really hacky parlor trick, use the transaction and multiple updates, it's much more intuitive.
Source: Stackoverflow.com