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
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'
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
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?