A colleague asked me this morning how to update a table in one database using data from a table in a completely separate database. I had to think for a moment as I don’t do this often (probably the lack of coffee), so as a gentle reminder to myself and hopefully you, here’s the syntax :
UPDATE table1 SET column1 = t2.column1
FROM database2.schema.table t2 //uses alias
WHERE t2.id = table1.id
An alias can also be used to represent tables in the same database. Just remember that SQL Server will read the FROM table first in order to validate that the columns specified exist, so although the query may appear a little back to front in terms of normal programming methods (using the alias before we define it in the order of the code), this is the correct syntax. SQL server will always validate your DB objects (tables, columns etc) prior to TSQL execution.