How do I UPDATE from a SELECT in SQL Server?

Shazam! Synchronization with MERGE

There's synchronizing two tables, then there's MERGE. MERGE is like a superhero—it can INSERT, UPDATE, and DELETE. All in one swoop!

MERGE INTO Table1 t1
USING Table2 t2
ON t1.id = t2.id
WHEN MATCHED THEN 
    UPDATE SET t1.col = t2.col;


MERGE allows actions like filtering and joining operations within its USING clause.

Correlated subqueries for precise updates

Dive deeper with correlated subqueries - the diving dolphins 🐬 of SQL! Isolate, target, avoid errors:

UPDATE Table1
SET col = (SELECT col FROM Table2 WHERE Table2.id = Table1.id AND Table2.col IS NOT NULL)
WHERE EXISTS (
    SELECT 1 FROM Table2 WHERE Table2.id = Table1.id AND Table2.col IS NOT NULL
);


Ta-da! 🎩Now watch those NULL values disappear!

Transactions: Keeping it atomic

Wrap your update in a transaction—think of it like a cozy blanket isolating your updates. Now, they remain warm and consistent even when users concurrently access the database:

BEGIN TRANSACTION;

UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.id = t2.id
WHERE t1.status = 'active';

COMMIT TRANSACTION;


Transaction: offering atomic joy to SQL everywhere!

The fine art of controlling quirks

Who doesn't enjoy crossing the t's and dotting the i's! Check your WHERE clause. Overzealous indexing? Overactive triggers? Keep them under control.

Broadcast updates to multiple columns

Update multiple columns, it's like shouting in a bustling marketplace. Make yourself heard!

UPDATE Table1
SET col1 = Table2.col1, 
    col2 = Table2.col2
FROM Table1
INNER JOIN Table2 ON Table1.id = Table2.id;


Just like that—a well-choreographed flashmob of data right across your database.

WHERE clause: Precision updating

Making surgery look sloppy since 1986. SQL UPDATEs with a WHERE clause can be highly selective. Scalpel, please!

UPDATE Table1 AS t1
SET t1.col = t2.col
FROM Table2 AS t2
WHERE t1.id = t2.id AND t1.col <> t2.col;


Voila! Surgery successful. Only value-differing rows underwent the procedure.