Microsoft SQL Server Join Using Join in an Update
Example
Joins can also be used in an UPDATE statement:
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
Update the SomeSetting column of the Preferences table filtering by a predicate on the Users table as follows:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p is an alias for Preferences defined in the FROM clause of the statement. Only rows with a matching AccountId from the Users table will be updated.
Update with left outer join statements
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Update tables with inner join and aggregate function
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3
