SQL Server UPDATE with SELECT: Complete Guide

In SQL Server, you can update a table using values from another table through several methods: UPDATE with JOIN syntax, UPDATE with subqueries, or using the MERGE statement. The most common approach is using UPDATE with FROM and JOIN clauses to update multiple columns from a source table based on matching conditions, while the MERGE statement provides a powerful all-in-one solution for conditional updates and inserts.

Contents

• Understanding SQL Server UPDATE with SELECT
• Method 1: UPDATE with JOIN Syntax
• Method 2: UPDATE with Subqueries
• Method 3: Using the MERGE Statement
• Method 4: Cursor-Based Updates
• Performance Considerations
• Complete Practical Examples

Understanding SQL Server UPDATE with SELECT

Unlike the INSERT…SELECT statement which is straightforward, updating a table with values from another table requires specific syntax in SQL Server. The basic concept remains similar - you want to set column values in a target table based on values from a source table, but the implementation requires understanding the different available approaches.

As Atlassian explains, SQL updates are typically performed using direct references, but sometimes you need to alter table contents indirectly using data from other tables. This is especially common when working with temporary tables or when synchronizing data between related tables.

Method 1: UPDATE with JOIN Syntax

The most common and efficient method for updating a table with values from another table is using UPDATE with JOIN. This approach allows you to join the target table with a source table and update columns based on the join conditions.

Basic Syntax

UPDATE target_table
SET target_table.column1 = source_table.column1,
    target_table.column2 = source_table.column2
FROM target_table
INNER JOIN source_table ON target_table.id = source_table.id
WHERE additional_conditions;

Practical Example

UPDATE products p
SET p.price = s.new_price,
    p.description = s.description
FROM products p
INNER JOIN price_updates s ON p.product_id = s.product_id
WHERE s.last_updated > DATEADD(MONTH, -1, GETDATE());

Key Points

• The JOIN condition determines which rows get updated
• You can use different JOIN types (INNER, LEFT, etc.) depending on your needs
• Column references must include the table alias when there might be ambiguity
• This method is generally more performant than subqueries for large datasets

As TechOnTheNet demonstrates, this syntax is widely used for updating one table with data from another table in SQL Server.

Method 2: UPDATE with Subqueries

Another approach is to use subqueries within the SET clause to update individual columns. This method is useful when you need to update single columns with complex logic or when working with correlated subqueries.

Basic Syntax

UPDATE target_table
SET column1 = (SELECT source_table.column1 
               FROM source_table 
               WHERE source_table.id = target_table.id),
    column2 = (SELECT source_table.column2 
               FROM source_table 
               WHERE source_table.id = target_table.id)
WHERE EXISTS (SELECT 1 FROM source_table 
              WHERE source_table.id = target_table.id);

Practical Example

UPDATE employees
SET salary = (SELECT new_salary FROM salary_adjustments 
              WHERE employee_id = employees.emp_id),
    department = (SELECT department FROM org_chart 
                  WHERE position_id = employees.position_id)
WHERE EXISTS (SELECT 1 FROM salary_adjustments 
              WHERE employee_id = employees.emp_id);

Key Points

• Each column gets its own subquery
• The subquery must return exactly one value per row
• Use WHERE EXISTS to ensure only rows with matching data get updated
• This method can be less efficient than JOIN for multiple columns

TutorialGateway provides examples of this approach, noting it’s excellent for updating single columns.

Method 3: Using the MERGE Statement

The MERGE statement is a powerful, all-in-one solution that can perform INSERT, UPDATE, and DELETE operations in a single statement. It’s particularly useful when you need to synchronize data between tables and handle both matching and non-matching rows.

Basic Syntax

MERGE target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1,
               target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (source.column1, source.column2);

Practical Example

MERGE products AS target
USING price_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET target.price = source.new_price,
               target.last_updated = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (product_id, price, last_updated)
    VALUES (source.product_id, source.new_price, GETDATE());

Key Points

• MERGE combines INSERT, UPDATE, and DELETE operations
• The ON clause defines the join condition
• WHEN MATCHED handles updates for existing rows
• WHEN NOT MATCHED handles inserts for new rows
• WHEN NOT MATCHED BY SOURCE can handle deletes (optional)
• This approach is atomic and transaction-safe

According to Microsoft Learn, MERGE provides a comprehensive solution for data synchronization tasks.

Method 4: Cursor-Based Updates

For complex update scenarios or when you need to process rows one by one with custom logic, you can use cursor-based updates. This approach is more complex and generally slower but offers maximum flexibility.

Basic Syntax

DECLARE update_cursor CURSOR FOR
SELECT target_id, new_value
FROM source_table;

DECLARE @target_id INT, @new_value DECIMAL;

OPEN update_cursor;
FETCH NEXT FROM update_cursor INTO @target_id, @new_value;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE target_table
    SET column = @new_value
    WHERE id = @target_id;
    
    FETCH NEXT FROM update_cursor INTO @target_id, @new_value;
END

CLOSE update_cursor;
DEALLOCATE update_cursor;

Key Points

• Cursors process rows sequentially
• Useful for complex business logic per row
• Generally slower than set-based operations
• Requires careful error handling and resource management
• Should be used sparingly for performance reasons

As shown in Microsoft Learn, cursor-based updates can be useful for specific scenarios but are not recommended for large datasets.

Performance Considerations

When choosing the right method for updating tables with SELECT statements, consider these performance factors:

JOIN vs Subquery Performance

• UPDATE with JOIN is generally faster for updating multiple columns from the same source table
• UPDATE with subqueries can be efficient for single column updates with complex logic
• Always test both approaches on your specific data volumes

Transaction Size

• Wrap large update operations in transactions
• Consider batching updates for very large datasets
• Use appropriate isolation levels to prevent blocking

Indexing Strategy

• Ensure join columns are properly indexed
• Consider creating temporary indexes for bulk operations
• Monitor performance using execution plans

Lock Considerations

• Large updates can cause blocking
• Consider using smaller batches
• Use appropriate locking hints when necessary

SQL Shack emphasizes the importance of choosing the right method based on your specific requirements and data characteristics.

Complete Practical Examples

Let’s put all these methods into practice with a complete example scenario.

Scenario Setup

-- Create target table
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10,2),
    LastUpdated DATETIME
);

-- Create source table with updates
CREATE TABLE dbo.EmployeeUpdates (
    EmployeeID INT,
    NewDepartment NVARCHAR(50),
    NewSalary DECIMAL(10,2),
    UpdateReason NVARCHAR(100)
);

-- Insert sample data
INSERT INTO dbo.Employees VALUES 
(1, 'John', 'Smith', 'IT', 60000.00, '2023-01-01'),
(2, 'Jane', 'Doe', 'HR', 55000.00, '2023-01-01'),
(3, 'Bob', 'Johnson', 'Finance', 70000.00, '2023-01-01');

INSERT INTO dbo.EmployeeUpdates VALUES 
(1, 'IT Management', 75000.00, 'Promotion'),
(2, 'HR Management', 65000.00, 'Promotion'),
(3, NULL, 72000.00, 'Merit increase');

Method 1: UPDATE with JOIN

UPDATE e
SET e.Department = eu.NewDepartment,
    e.Salary = eu.NewSalary,
    e.LastUpdated = GETDATE()
FROM dbo.Employees e
INNER JOIN dbo.EmployeeUpdates eu ON e.EmployeeID = eu.EmployeeID
WHERE eu.NewDepartment IS NOT NULL OR eu.NewSalary IS NOT NULL;

Method 2: UPDATE with Subqueries

UPDATE dbo.Employees
SET Department = (SELECT NewDepartment FROM dbo.EmployeeUpdates 
                  WHERE EmployeeID = Employees.EmployeeID),
    Salary = (SELECT NewSalary FROM dbo.EmployeeUpdates 
              WHERE EmployeeID = Employees.EmployeeID),
    LastUpdated = GETDATE()
WHERE EXISTS (SELECT 1 FROM dbo.EmployeeUpdates 
              WHERE EmployeeID = Employees.EmployeeID);

Method 3: MERGE Statement

MERGE INTO dbo.Employees AS target
USING dbo.EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED AND (source.NewDepartment IS NOT NULL OR source.NewSalary IS NOT NULL) THEN
    UPDATE SET 
        target.Department = ISNULL(source.NewDepartment, target.Department),
        target.Salary = ISNULL(source.NewSalary, target.Salary),
        target.LastUpdated = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, FirstName, LastName, Department, Salary, LastUpdated)
    SELECT EmployeeID, 'New', 'Employee', NewDepartment, NewSalary, GETDATE()
    FROM dbo.EmployeeUpdates
    WHERE EmployeeID NOT IN (SELECT EmployeeID FROM dbo.Employees);

Verification

-- Check updated data
SELECT * FROM dbo.Employees ORDER BY EmployeeID;

-- Check what updates were applied
SELECT e.EmployeeID, 
       e.Department AS OldDepartment,
       eu.NewDepartment,
       e.Salary AS OldSalary,
       eu.NewSalary,
       eu.UpdateReason
FROM dbo.Employees e
LEFT JOIN dbo.EmployeeUpdates eu ON e.EmployeeID = eu.EmployeeID;

This comprehensive example demonstrates how to effectively use different methods to update a table with values from another table in SQL Server.

Sources

1. How to UPDATE from a SELECT statement in SQL Server - SQL Shack
2. UPDATE (Transact-SQL) - SQL Server | Microsoft Learn
3. How to UPDATE from SELECT in SQL Server | Atlassian
4. SQL Server: UPDATE Statement - TechOnTheNet
5. How to UPDATE from SELECT in SQL Server - TutorialGateway
6. SQL UPDATE from SELECT, JOIN or MERGE - MSSQLTips
7. MERGE (Transact-SQL) - SQL Server | Microsoft Learn
8. SQL Server UPDATE JOIN Explained By Practical Examples - SQL Server Tutorial

Conclusion

SQL Server provides multiple effective methods for updating tables using values from other tables. The UPDATE with JOIN syntax is generally the most efficient for straightforward updates, while the MERGE statement offers the most comprehensive solution for data synchronization tasks. When working with temporary tables or complex logic, subqueries can be particularly useful. Always consider performance implications, proper indexing, and transaction management when implementing these techniques. Choose the method that best fits your specific requirements and data characteristics for optimal results.