Perform UPDATE statement from a SELECT with SQL

It’s quite common where I need to perform an UPDATE statement with SQL but it is not a garden variety single table update statement. Instead I need to join data from another table, whether it is to get data from the second table or use it in my WHERE clause.

SQL Update from Select Statement

Let’s assume we have two tables: Table1 and Table2. Table2 has a foreign key to table one so we can perform the join on. A standard select statement for these would be:

[code]
SELECT * FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Table1Id
[/code]

In my update statement I want to do something similar where I need to set data from Table2 on Table1 via an update statement:

[code]
UPDATE t1
SET t1.Name = t2.Name
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Table1Id
[/code]

This can be further extended to add a where clause if need be to further filter down the data:

[code]
UPDATE t1
SET t1.Name = t2.Name
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Table1Id
WHERE t2.Country = ‘Canada’
[/code]