How to update rows in a table directly from a Select statement in SQL Server?
Technical tutorial to update a table directly from an select in SQL Server. Indeed, generally, in data integration projects, it often involves to update table based on the data from another table. This is particularly common in scenarios where you have separate but related datasets, such as a source table with new sales data and a target table where this data needs to be integrated.
Here, we will explore how to update a table named SalesDataTarget from a table named SalesDataSource using three methods: using SELECT with INNER JOIN, the MERGE INTO option and using a temporary table. Before diving into the queries, let’s define below the structure of our example tables.
Table of Contents
1. Prepare SQL Server tables for the update
In the source table and also in the updated target table, we use these 5 columns:
And an additional metadata column in the table to update, to store the modified date.
Create the source table and populate it
Use this script to create the first table.
CREATE TABLE SalesDataSource ( Month INT, Year INT, Product VARCHAR(100), Amount DECIMAL(10, 2), Quantity INT );
Then insert a few sample rows to illustrate how the update query works.
INSERT INTO SalesDataSource (Month, Year, Product, Amount, Quantity) VALUES (1, 2023, 'ProductA', 100.00, 10), (1, 2023, 'ProductB', 200.00, 20), (2, 2023, 'ProductA', 150.00, 15), (2, 2023, 'ProductB', 250.00, 25), (3, 2023, 'ProductA', 120.00, 12), (3, 2023, 'ProductB', 220.00, 22), (4, 2023, 'ProductA', 130.00, 13), (4, 2023, 'ProductB', 230.00, 23);
Create and populate the table to update
CREATE TABLE SalesDataTarget ( Month INT, Year INT, Product VARCHAR(100), Amount DECIMAL(10, 2), Quantity INT, ModifiedDate DATETIME ); INSERT INTO SalesDataTarget (Month, Year, Product, Amount, Quantity, ModifiedDate) VALUES (1, 2023, 'ProductA', 90.00, 9, GETDATE()), (1, 2023, 'ProductB', 190.00, 19, GETDATE()), (2, 2023, 'ProductA', 140.00, 14, GETDATE()), (2, 2023, 'ProductB', 240.00, 24, GETDATE()), (3, 2023, 'ProductA', 110.00, 11, GETDATE()), (3, 2023, 'ProductB', 210.00, 21, GETDATE()), (4, 2023, 'ProductA', 130.00, 13, GETDATE()), (4, 2023, 'ProductB', 230.00, 23, GETDATE());
2. Use Select and Inner Join to Update the table
The SELECT statement combined with INNER JOIN is a straightforward approach for updating records. This method is particularly useful when you need to update existing records in the target table based on matching criteria.
UPDATE T SET T.Amount = S.Amount, T.Quantity = S.Quantity, T.ModifiedDate = GETDATE() FROM SalesDataTarget AS T INNER JOIN SalesDataSource AS S ON T.Month = S.Month AND T.Year = S.Year AND T.Product = S.Product WHERE /* Add here conditions to filter rows */ /* do not forget the alias of the table */ ;
In the query above, the SalesDataTarget table is updated with the Amount and Quantity from SalesDataSource, where the Month, Year, and Product columns match. The ModifiedDate is set to the current date and time.
3. Use the Merge Into for update or insert
The MERGE INTO statement allows updating, inserting, and deleting. So it is more versatile and can be used for complex operations like simultaneous .
MERGE INTO SalesDataTarget AS T USING SalesDataSource AS S ON T.Month = S.Month AND T.Year = S.Year AND T.Product = S.Product WHEN MATCHED THEN UPDATE SET T.Amount = S.Amount, T.Quantity = S.Quantity, T.ModifiedDate = GETDATE() WHEN NOT MATCHED BY TARGET THEN INSERT (Month, Year, Product, Amount, Quantity, ModifiedDate) VALUES (S.Month, S.Year, S.Product, S.Amount, S.Quantity, GETDATE()) ;
In this MERGE INTO statement, the SalesDataTarget table is updated with values from SalesDataSource based on matching
Product. If a matching row doesn’t exist in the target, a new row is inserted.
4. T-SQL update script with a temporary table for performance
In the script below, we perform these actions:
- A temporary table named #TempSalesData is created, mirroring the structure of SalesDataSource.
- Data is inserted into #TempSalesData from SalesDataSource.
- SalesDataTarget is updated using the data from #TempSalesData, with matching on Month, Year, and Product. The ModifiedDate in SalesDataTarget is updated to the current date and time.
- Finally, the temporary table is dropped with DROP TABLE.
This approach is useful when you need to perform intermediate operations on the data before updating the target table, or when you want to reduce the load on the original source table during complex operations. Especially if you load a very large amount of data and you only want to use the selected data in the join of the update operation.
Temporary tables are session-specific in SQL Server and are automatically dropped when the session ends, but it’s good practice to explicitly drop them as shown.
-- Create a temporary table with the same structure as SalesDataSource CREATE TABLE #TempSalesData ( Month INT, Year INT, Product VARCHAR(100), Amount DECIMAL(10, 2), Quantity INT ); -- Insert data into the temporary table from SalesDataSource INSERT INTO #TempSalesData (Month, Year, Product, Amount, Quantity) SELECT Month, Year, Product, Amount, Quantity FROM SalesDataSource WHERE Product = 'ProductA' -- filter example AND Month = 1; -- filter example -- Update SalesDataTarget using the temporary table UPDATE Target SET Target.Amount = Temp.Amount, Target.Quantity = Temp.Quantity, Target.ModifiedDate = GETDATE() FROM SalesDataTarget AS Target INNER JOIN #TempSalesData AS Temp ON Target.Month = Temp.Month AND Target.Year = Temp.Year AND Target.Product = Temp.Product; -- Drop the temporary table when done DROP TABLE #TempSalesData;
Different approaches to update tables with slightly different outputs
Both these methods are effective for synchronizing data between two tables in SQL Server. The choice of method depends on your specific requirements and the complexity of the data operation. The
INNER JOIN is ideal for straightforward updates, while
MERGE INTO provides a more comprehensive solution for handling multiple types of data operations in a single statement.