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.
- Get all employee's salary info where the grade is C.
- 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'
-
CreatedJan 26, 2020
-
UpdatedNov 03, 2020
-
Views1,470
Related Articles
Different types of variables in SQL Server
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?