How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it's possible to insert it into a table using a SELECT statement. Similarly, we can do update the table based on the SELECT statement.

Basic Syntax:

UPDATE		Table_A
SET			Table_A.Col1 = Table_B.Col1
			, Table_A.Col2 = Table_B.Col2

FROM		Some_Table AS Table_A
INNER JOIN	Other_Table AS Table_B ON Table_A.Id = Table_B.Id

WHERE		Table_A.Col3 = 'some value'

Example:

This article will use two table variables @Employee and @Salary.

Query to create tables with sample data.

DECLARE @Employee TABLE (EmployeeId INT, EmployeeName VARCHAR(100), Grade CHAR(1))
DECLARE @Salary TABLE (SalaryId INT, EmployeeId INT, Amount INT)

-- Insert into @Employee
INSERT INTO @Employee (EmployeeId, EmployeeName, Grade)
SELECT 101, 'Vikram', 'A' UNION
SELECT 102, 'Vijay', 'A' UNION
SELECT 103, 'Vinod', 'B' UNION
SELECT 104, 'Victor', 'C' UNION
SELECT 105, 'Vishal', 'C'

-- Insert into @Salary
INSERT INTO @Salary (SalaryId, EmployeeId, Amount)
SELECT 1, 101, 10000 UNION
SELECT 2, 102, 10000 UNION
SELECT 3, 103, 3000 UNION
SELECT 3, 104, 3000 UNION
SELECT 3, 105, 3000

Update the employee salary to 5000, where employee grade is C.

We will do this in two steps.

1. Get all employee's salary info where the grade is C.
2. Update employee salary to 5000.

1) Get all employee's salary info where the grade is C.

SELECT		E.EmployeeId, E.EmployeeName, E.Grade, S.SalaryId, S.Amount
FROM		@Employee E
INNER JOIN	@Salary S ON E.EmployeeId = S.EmployeeId
WHERE		E.Grade = 'C'

2) Update employee salary to 5000.

UPDATE		S
SET			S.Amount = 5000
FROM		@Employee E
INNER JOIN	@Salary S ON E.EmployeeId = S.EmployeeId
WHERE		E.Grade = 'C'