Update statement using join in SQL server

Join clause can also be used in update statement. Let's see the below example. It can be used in different way is mentioned below.

Update using simple join statement:

Create table Users and Preferences.

CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)

Create the second table.

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

(Demonstrated the update statement format only)

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