tsql : what's wrong with this multiple table update statement?

Question

I have the following update statement:

update db1.dbo.table1 set db1.dbo.table1.col = db1_bk.dbo.table1.col where db1.dbo.table1.id = db1_bk.dbo.table1.id

no error.. it just won't update the table row.
however if i run update db1.dbo.table1 set db1.dbo.table1.col = '2010-01-01' where db1.dbo.table1.id = 2 it does work.

any ideas?

Answer 1

you have 2 tables listed after update, which you cannot do.

The proper query is:

update X set x.col = a.col
from db1.dbo.table1 X
join db1_bk.dbo.table1 a
on X.id = a.id

Answer 2

You haven't formed your query properly...

update db1.dbo.table1
set db1.dbo.table1.col = db1_bk.dbo.table1.col 
From db1.dbo.table1, db1_bk.dbo.table1
where db1.dbo.table1.id = db1_bk.dbo.table1.id

But I would join explicitly... no more efficient, but clearer IMHO:

update tbl1
set tbl1.col = bk.col 
From db1.dbo.table1 tbl1
inner join db1_bk.dbo.table1 bk
on tbl1.id = bk.id

Answer 3

UPDATE  db1.dbo.table1
SET     col = db1_bk.dbo.table1.col
FROM    db1.dbo.table1
JOIN    db1_bk.dbo.table1
ON      db1_bk.dbo.table1.id = db1.dbo.table1.id