SQL UPDATE Using SELECT in SQL Server

General Discussions
, ,
1

How do I perform an SQL UPDATE from SELECT in SQL Server?

In SQL Server, I know it’s possible to insert rows into a table using an INSERT ... SELECT statement:

INSERT INTO Table (col1, col2, col3)  
SELECT col1, col2, col3  
FROM other_table  
WHERE sql = 'cool';

But is it also possible to update a table using a SELECT statement? I have a temporary table with values and want to update another table using those values. Something like this:

UPDATE Table SET col1, col2  
SELECT col1, col2  
FROM other_table  
WHERE sql = 'cool'  
WHERE Table.id = other_table.id;

How can I correctly use SQL UPDATE from SELECT to achieve this?

2

If you’re working with SQL Server, the most straightforward way to handle an SQL UPDATE from SELECT is using an UPDATE ... FROM with a JOIN. Here’s how you can do it:

UPDATE t  
SET t.col1 = s.col1,  
    t.col2 = s.col2  
FROM Table t  
INNER JOIN other_table s ON t.id = s.id  
WHERE s.sql = 'cool';

:point_right: This method ensures that only matching records get updated, making it efficient and easy to read. It’s the go-to approach when you need a direct SQL UPDATE from SELECT solution.

3

Great point! But if you need more control over how updates happen, MERGE is an even more powerful alternative. It allows you to not only update records but also insert or delete them based on conditions.

MERGE INTO Table AS t  
USING other_table AS s  
ON t.id = s.id  
WHEN MATCHED AND s.sql = 'cool'  
THEN UPDATE  
SET t.col1 = s.col1,  
    t.col2 = s.col2;

:point_right: With MERGE, you get flexibility—you can extend it to handle scenarios where the record doesn’t exist and needs an insert instead. If your use case goes beyond a simple SQL UPDATE from SELECT, MERGE is your best bet!

4

Both UPDATE ... FROM and MERGE are great approaches! But if you want cleaner syntax and better readability, consider using a Common Table Expression (CTE).

WITH CTE AS (  
    SELECT t.id, s.col1, s.col2  
    FROM Table t  
    INNER JOIN other_table s ON t.id = s.id  
    WHERE s.sql = 'cool'  
)  
UPDATE t  
SET t.col1 = CTE.col1,  
    t.col2 = CTE.col2  
FROM Table t  
INNER JOIN CTE ON t.id = CTE.id;

:point_right: A CTE makes complex queries easier to debug and maintain while achieving the same SQL UPDATE from SELECT logic. Plus, if you need to extend it for more transformations, it keeps things modular and structured.