SQL : Update a table from data in another database


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 :

USE database1

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s