Update a table from a Select in SQL Server

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:

Month
Year
Product
Amount
Quantity

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 Month, Year, and 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 SELECT with 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.