Answer: We can update a table using the UPDATE with JOIN from a SELECT in SQL Server.
In SQL Server, you can update records in a table based on data from another table using the UPDATE statement with a JOIN or a FROM clause. There are a few methods to achieve this. In this blog, let’s look into all the methods in detail along with examples.
Table of Contents:
Methods to UPDATE from a SELECT in SQL Server
Before we begin with the methods to update from a SELECT in SQL server, let’s create two tables that can be used for further methods as an example in this blog.
Firstly, we create two tables named Employee and SalaryUpdates, which will be used in this blog for further methods as an example.
The first table is the Employee table which consists of EmployeeID and Salary and here is how the table is created in the SQL Server.
--Create the Employee table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL(10, 2)
);
--Insert the provided data into the Employee table
INSERT INTO Employees (EmployeeID, Salary) VALUES
(1, 45000),
(2, 50000),
(3, 55000),
(4, 60000);
--To display the created Employees table
Select * from Employee;
This is how the employee table looks once it is created and all the values are inserted into it.
The second table is the SalaryUpdates table which consists of EmployeeID and NewSalary and here is how the table can be created and inserted.
--Create the SalaryUpdates table
CREATE TABLE SalaryUpdates (
EmployeeID INT PRIMARY KEY,
NewSalary DECIMAL(10, 2)
);
--Insert the provided data into the SalaryUpdates table
INSERT INTO SalaryUpdates (EmployeeID, NewSalary) VALUES
(1, 48000),
(2, NULL),
(3, 57000),
(5, 62000);
--To display the created SalaryUpdates table
Select * from SalaryUpdates;
This would be the output of the SalaryUpdates table after creating and inserting the values into the table.
Method 1: Using UPDATE with JOIN in SQL Server
This UPDATE with the JOIN method updates the records in a target table concerning the other table which allows us to modify the data using the related information on the other table.
Syntax:
UPDATE t
SET t.ColumnToUpdate = s.NewValue
FROM TargetTable t
INNER JOIN SourceTable s ON t.MatchColumn = s.MatchColumn
WHERE <optional conditions>;
Now we should update the salary in the employee table using the NewSalary in the SalaryUpdates table
Query:
UPDATE e
SET e.Salary = su.NewSalary
FROM Employees e
INNER JOIN SalaryUpdates su ON e.EmployeeID = su.EmployeeID
WHERE su.NewSalary IS NOT NULL;
Output:
Explanation: It updates the Salary in the Employees table (e.Salary) with the value from the NewSalary column in the SalaryUpdates table (su.NewSalary).
Method 2: Using UPDATE with FROM in SQL Server
When dealing with complex data relationships this UPDATE with FROM clause is useful. It will allow us to update data in the target variable based on the data from another table or even using a subquery.
Syntax:
UPDATE TargetTable
SET TargetTable.ColumnToUpdate = SourceTable.NewValue
FROM TargetTable
INNER JOIN (SELECT ... FROM SourceTable) AS SourceTable
ON TargetTable.MatchColumn = SourceTable.MatchColumn
WHERE <optional conditions>;
Query:
UPDATE e
SET e.Salary = su.NewSalary
FROM Employees e
INNER JOIN (
SELECT EmployeeID, NewSalary
FROM SalaryUpdates
WHERE NewSalary IS NOT NULL
) AS su
ON e.EmployeeID = su.EmployeeID;
Output:
Explanation:
- So the subquery (select EmployeeID, NewSalary FROM SalaryUpdates WHERE NewSalary IS NOT NULL) will select only the records from SalaryUpdates where new salary is NOT NULL.
- The inner join condition joins the subquery based on the Employee ID.
Method 3: UPDATE from SELECT using the JOIN method in SQL Server
This approach is useful in working with related tables in the normalized databases. It is used to update multiple rows in a single query without updating them individually.
Syntax:
UPDATE target_table AS t
JOIN source_table AS s
ON t.common_column = s.common_column
SET t.column_to_update = s.new_value
WHERE condition;
Query:
UPDATE Employees AS e
JOIN SalaryUpdates AS s
ON e.EmployeeID = s.EmployeeID
SET e.Salary = s.NewSalary
WHERE s.NewSalary IS NOT NULL;
Output:
Explanation:
- The join statement joins the employees (e) with SalaryUpdates (s) based on the EmployeeID to match the records that need the updation.
- Where statements ensure that only records with the non-NULL NewSalary are updated which prevents the overwriting of existing salaries with NULL values.
Method 4: UPDATE from SELECT using the MERGE statement in SQL Server
The merge statement is also known as upsert which is a combination of update and insert. This command is used to update, insert, or delete the records in the target table based on the matching conditions in the source table.
Syntax:
MERGE INTO target_table AS t
USING source_table AS s
ON t.common_column = s.common_column
WHEN MATCHED THEN
UPDATE SET t.column_to_update = s.new_value
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...)
VALUES (s.column1, s.column2, ...);
Query:
MERGE INTO Employees AS e
USING SalaryUpdates AS s
ON e.EmployeeID = s.EmployeeID
WHEN MATCHED AND s.NewSalary IS NOT NULL THEN
UPDATE SET e.Salary = s.NewSalary
WHEN NOT MATCHED AND s.NewSalary IS NOT NULL THEN
INSERT (EmployeeID, Salary)
VALUES (s.EmployeeID, s.NewSalary);
Output:
Explanation:
The 48000 is updated, 50000(No change because the new salary is NULL), 57000 (Updated), 60000 (No change since no matching in SalaryUpdates), and 62000(Inserted because the new employee is added).
Method 5: UPDATE from SELECT using the subquery method in SQL Server
This update is flexible and useful when we want to perform conditional updates based on the result of the Select statement.
Syntax:
UPDATE target_table
SET column_to_update = (
SELECT new_value
FROM source_table
WHERE target_table.common_column = source_table.common_column
)
WHERE EXISTS (
SELECT 1
FROM source_table
WHERE target_table.common_column = source_table.common_column
);
Query:
UPDATE Employees
SET Salary = (
SELECT s.NewSalary
FROM SalaryUpdates AS s
WHERE Employees.EmployeeID = s.EmployeeID
)
WHERE EmployeeID IN (
SELECT EmployeeID
FROM SalaryUpdates
WHERE NewSalary IS NOT NULL
);
Output:
Explanation:
- The update command specifies the target table where the data will be updated.
- The subquery retrieves the new salary (New Salary) from the SalaryUpdates table for the matching EmployeeID.
Method 6: Using UPDATE with CTE in SQL Server
In SQL Server, we can use a CTE(Common Table Expression) to update records in a table. This method makes queries more readable, when dealing with complex filtering and joins.
Syntax:
WITH CTE_Name AS (
SELECT Column1, Column2, ...
FROM TableName
WHERE Condition
)
UPDATE CTE_Name
SET Column1 = NewValue
WHERE another_condition;
Query:
WITH IT_Employees AS (
SELECT EmployeeID, Salary
FROM Employees
WHERE EmployeeID = 2
)
UPDATE e
SET e.Salary = e.Salary * 1.10
FROM Employees e
JOIN IT_Employees it
ON e.EmployeeID = it.EmployeeID;
Output:
Explanation:
CTE (IT_Employees) selects only EmployeeID = 2 with its Salary from Employees.
| Method | Use case | Pros | Cons |
| Using UPDATE with JOIN | Used for updating data from another table based on the common key | Avoids multiple subqueries | Slows down the process if the joins are complex |
| Using UPDATE with FROM | Simple updates can be done if there aren’t any complex conditions | Less memory intensive than subqueries | Not flexible for complex filtering |
| UPDATE from SELECT Using JOIN | Updating based on the SELECT statement with multiple conditions | Works well when filtering is needed and is also good for targeted updates | Performance loss with large datasets |
| Using MERGE for UPDATE | It is used for bulk updates and also for handling inserts/deletes in one step | Efficient when handling large datasets | Requires careful indexing since it leads to deadlocks |
| UPDATE Using Subquery | Updates based on a single condition | Simple when updating from a single column | Can be inefficient if the subquery runs multiple times |
| Using UPDATE with CTE | Update with the temporary results set | Makes complex updates easy to understand | Not always faster for complex updates |
Conclusion
For simple updates, UPDATE with JOIN is faster and clearer, while for complex updates, UPDATE with FROM subquery offers more flexibility. Understanding these methods helps you to effectively UPDATE using a SELECT statement in SQL Server.