“`html
Response: We are capable of modifying a table utilizing the UPDATE combined with JOIN from a SELECT in SQL Server.
In SQL Server, you can alter records in a table by relying on data from another table with the UPDATE statement combined with a JOIN or a FROM clause. Several approaches can achieve this. In this article, let’s explore all the approaches thoroughly with examples.
Contents:
- Approaches to UPDATE from a SELECT in SQL Server
- Approach 1: Utilizing UPDATE with JOIN in SQL Server
- Approach 2: Utilizing UPDATE with FROM in SQL Server
- Approach 3: UPDATE from SELECT employing the JOIN methodology in SQL Server
- Approach 4: UPDATE from SELECT using the MERGE statement in SQL Server
- Approach 5: UPDATE from SELECT utilizing the subquery approach in SQL Server
- Approach 6: Utilizing UPDATE with CTE in SQL Server
- Performance evaluation for each approach
- Final Thoughts
Approaches to UPDATE from a SELECT in SQL Server
Before diving into the approaches for updating from a SELECT in SQL Server, let’s establish two tables that will serve as examples for the subsequent methods discussed in this article.
To start, we will create two tables titled Employee and SalaryUpdates, which will be referenced in this article for demonstration purposes.
The first table is the Employee table, containing EmployeeID and Salary. Below is the SQL Server command for creating the table.
--Create the Employee table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL(10, 2)
);
--Insert the specified data into the Employee table
INSERT INTO Employees (EmployeeID, Salary) VALUES
(1, 45000),
(2, 50000),
(3, 55000),
(4, 60000);
--To view the created Employees table
Select * from Employees;
This represents the Employee table after creation and insertion of all corresponding values.
The second table is the SalaryUpdates table, which comprises EmployeeID and NewSalary. Here’s how to create and populate this table.
--Create the SalaryUpdates table
CREATE TABLE SalaryUpdates (
EmployeeID INT PRIMARY KEY,
NewSalary DECIMAL(10, 2)
);
--Insert the specified data into the SalaryUpdates table
INSERT INTO SalaryUpdates (EmployeeID, NewSalary) VALUES
(1, 48000),
(2, NULL),
(3, 57000),
(5, 62000);
--To view the created SalaryUpdates table
Select * from SalaryUpdates;
Below is the result of the SalaryUpdates table following its creation and value insertion.
Approach 1: Utilizing UPDATE with JOIN in SQL Server
This UPDATE combined with the JOIN method allows for modification of records in a target table in relation to another table, enabling data alteration using correlated information from 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>;Next, we’ll adjust the salary in the employee table using the NewSalary values found 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:
Clarification: This command updates the Salary within the Employees table (e.Salary) using the value from the NewSalary column in the SalaryUpdates table (su.NewSalary).
Approach 2: Utilizing UPDATE with FROM in SQL Server
In scenarios involving intricate data relationships, this UPDATE with FROM clause is quite beneficial. It allows us to modify data in the target entity based on information sourced from another table or even through 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:
Clarification:
- The subquery (select EmployeeID, NewSalary FROM SalaryUpdates WHERE NewSalary IS NOT NULL) will extract only the records from SalaryUpdates where the new salary is NOT NULL.
- The inner join condition merges the subquery based on the Employee ID.
Approach 3: UPDATE from SELECT employing the JOIN methodology in SQL Server
This technique is advantageous when dealing with associated tables in normalized databases. It enables the updating of multiple rows in a singular query without necessitating individual updates.
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
``````sql
e.EmployeeID = s.EmployeeID
SET e.Salary = s.NewSalary
WHERE s.NewSalary IS NOT NULL;Result:
Clarification:
- The join clause connects the employees (e) with SalaryUpdates (s) using the EmployeeID to identify the records requiring modification.
- The where clause guarantees that only records with a non-NULL NewSalary are modified, thus preventing overwriting existing salaries with NULL values.
Method 4: UPDATE from SELECT using the MERGE statement in SQL Server
The merge clause, commonly referred to as upsert, is an amalgamation of update and insert. This instruction is utilized to update, insert, or remove records in the target table based on the corresponding conditions in the source table.
Format:
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, ...);Instruction:
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);Result:
Clarification:
The 48000 is modified, 50000(No alteration because the new salary is NULL), 57000 (Modified), 60000 (No alteration since there is no corresponding entry in SalaryUpdates), and 62000(Inserted due to the addition of the new employee).
Method 5: UPDATE from SELECT using the subquery approach in SQL Server
This update method is adaptable and beneficial when conditional updates are required based on the outcomes of the Select statement.
Format:
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
);Instruction:
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
);Result:
Clarification:
- The update statement defines the target table where the data will undergo modifications.
- The subquery obtains the new salary (New Salary) from the SalaryUpdates table for the corresponding EmployeeID.
Method 6: Employing UPDATE with CTE in SQL Server
In SQL Server, a CTE (Common Table Expression) can be utilized to adjust records in a table. This approach enhances query readability, particularly when managing intricate filtering and joins.
Format:
WITH CTE_Name AS (
SELECT Column1, Column2, ...
FROM TableName
WHERE Condition
)
UPDATE CTE_Name
SET Column1 = NewValue
WHERE another_condition;Instruction:
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;Result:
Clarification:
CTE (IT_Employees) selects the record with EmployeeID = 2 and its corresponding Salary from Employees.
Method performance comparison for each tactic
| Tactic | Scenario | Advantages | Disadvantages |
| Using UPDATE with JOIN | Applicable for altering data from another table relying on a shared key | Minimizes the use of multiple subqueries | Can slow down operations with intricate joins |
| Using UPDATE with FROM | Simple modifications can be executed if there are no complex conditions | Less demanding on memory compared to subqueries | Limited flexibility for complex filtering |
| UPDATE from SELECT Using JOIN | Updating based on the SELECT query with multiple conditions | Effective when filtering is necessary and is beneficial for precise updates | Performance degradation with larger datasets |
| Utilizing MERGE for UPDATE | Utilized for bulk modifications and also for managing inserts/deletes in a single step | Effective when processing large datasets | Demands meticulous indexing as it may cause deadlocks |
| UPDATE Using Subquery | Updates predicated on a singular condition | Straightforward when updating from one column | May prove inefficient if the subquery is executed multiple times |
| Using UPDATE with CTE | Modifications corresponding to temporary result sets | Simplifies understanding of complex updates | Not invariably faster for intricate updates |
Conclusion
For straightforward updates, UPDATE with JOIN is quicker and more transparent, while for more elaborate updates, UPDATE with FROM subquery provides enhanced flexibility. Grasping these techniques enables effective UPDATE operations utilizing a SELECT statement in SQL Server.
The article How to UPDATE from a SELECT in SQL Server? was first featured on Intellipaat Blog.
“`