37

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?

Coyolero
  • 2,353
  • 4
  • 25
  • 34
  • 2
    Failing how? Error message? Just not updating what you want? – Eli Gassert Feb 27 '13 at 15:22
  • 5
    `UPDATE` aside, please, please, please stop writing old-style joins. Use proper `INNER JOIN` syntax. https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx – Aaron Bertrand Feb 27 '13 at 16:08

2 Answers2

61

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
jazzytomato
  • 6,994
  • 2
  • 31
  • 44
  • Thanks. Is it a good practice to put the keyword `end` after `commit`? or Is not necessary? – Coyolero Feb 27 '13 at 15:48
  • 5
    *BEGIN TRANSACTION* is not the same as *BEGIN* which starts a block ( used in *IF* for example). So don't use *END*, this not a even question of good or bad practice, you might close the block you're in and find unexpected output – jazzytomato Feb 27 '13 at 15:55
25

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.

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
  • 3
    Your bolded statement isn't quite true. You can update a table based on a join, you just can't have the update *affect* multiple tables. And I agree about the hack-i-ness of the suggested link (I might even suggest removing it). Is it really worth doing all that to avoid writing two statements? – Aaron Bertrand Feb 27 '13 at 15:50
  • The link doesn't describe how to use a union, maybe it was another post from that blog? – Bruno Martinez Jul 20 '16 at 03:48
  • @BrunoMartinez You're right, the person at that link received so much traffic (it's a link from 2013) that they changed the content to a bunch of questionable stuff. So I removed it. – Eric Leschinski May 05 '21 at 15:51