SQL Server Update Table From Another Table

by

In this article, I will walk you through professional-grade methods for performing cross-table updates in SQL Server, from standard joins to advanced merge logic.

SQL Server Update Table From Another Table

Manual updates are out of the question for anything over a dozen rows. We need a way to tell SQL Server: “Look at Table B, find the matching ID in Table A, and change the price to match.”

Method 1: The UPDATE JOIN Syntax (The “Industry Standard”)

In my experience, the UPDATE...FROM...JOIN syntax is the most readable and commonly used method in American dev shops. It allows you to use the familiar JOIN logic to link your target and source.

The Logic and Syntax

The key here is that the UPDATE clause refers to the alias of the table, not the full table name again in the middle of the statement.

SQL

UPDATE target
SET target.RetailPrice = source.NewPrice,
    target.LastUpdated = GETDATE()
FROM Production_Products AS target
INNER JOIN Staging_PriceUpdate AS source
    ON target.ProductID = source.ProductID;

Why This Works Well

  • Efficiency: It uses the SQL Query Optimizer to find the most efficient path between indexes.
  • Multi-Column Updates: You can update ten columns just as easily as one.
  • Filtering: You can add a WHERE clause to ensure you only update rows that actually changed.

Method 2: The MERGE Statement

If you are a Senior Architect at a tech firm, you likely prefer the MERGE statement. Introduced in SQL Server 2008, MERGE (often called “UPSERT”) allows you to handle Updates, Inserts, and Deletions in a single atomic transaction.

When to Use MERGE

Use MERGE when the source table might contain new rows that don’t exist in the target yet, or when you want to handle “Deletes” for rows no longer present in the source.

ActionScenario
MATCHEDThe ID exists in both; Update the target.
NOT MATCHED BY TARGETThe ID is in Source but not Target; Insert it.
NOT MATCHED BY SOURCEThe ID is in Target but not Source; Delete it (optional).

MERGE Example Logic

SQL

MERGE Production_Inventory AS Target
USING Staging_Inventory AS Source
ON (Target.SKU = Source.SKU)
WHEN MATCHED THEN
    UPDATE SET Target.Quantity = Source.Quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (SKU, Quantity) VALUES (Source.SKU, Source.Quantity);

Method 3: The Subquery Approach

Before the JOIN syntax became the standard, we used subqueries. While I find this less readable for large datasets, it is useful for simple, single-column updates.

SQL

UPDATE Production_Products
SET RetailPrice = (SELECT NewPrice 
                   FROM Staging_PriceUpdate 
                   WHERE Staging_PriceUpdate.ProductID = Production_Products.ProductID)
WHERE EXISTS (SELECT 1 
              FROM Staging_PriceUpdate 
              WHERE Staging_PriceUpdate.ProductID = Production_Products.ProductID);

Warning: The WHERE EXISTS clause is mandatory here. Without it, any row in your target table that doesn’t have a match in the source will have its value set to NULL.

Performance Benchmarking: Which Method is Faster?

MethodSpeed (Small Data)Speed (Large Data)Complexity
UPDATE JOINExcellentExcellentLow
MERGEGoodModerateMedium
SubqueryGoodPoorMedium

Key Takeaway: For pure updates, the JOIN syntax usually wins on performance. MERGE carries slightly more overhead because it has to manage the logic for three different potential actions.

Critical Safety Measures

Updating data is a destructive action. Once you COMMIT, the old data is gone. In my decade of consulting, I follow a strict protocol before running any cross-table update.

1. The “Select Before Update” Rule

Never run an UPDATE without first running it as a SELECT.

  • Change your UPDATE target SET... to SELECT target.OldVal, source.NewVal.
  • Verify at least 10–20 rows manually to ensure your JOIN logic isn’t creating a “Cartesian Product” (where every row matches every other row).

2. Use Transactions

Always wrap your update in a transaction block. This is the “Undo” button of the SQL world.

SQL

BEGIN TRANSACTION;

-- Run your update here
UPDATE target ...

-- CHECK YOUR RESULTS!
-- SELECT COUNT(*) FROM target WHERE ...

-- If it looks good:
COMMIT;

-- If you messed up:
-- ROLLBACK;

3. Check Row Counts

After the update, SQL Server will report (X row(s) affected). If you expected to update 500 rows and it says 5,000,000, hit ROLLBACK immediately.

Best Practices

If you are working with tables containing hundreds of millions of rows, standard updates can bloat the Transaction Log.

  1. Batching: Update in chunks of 5,000 or 10,000 rows using a WHILE loop. This prevents the log from filling up the disk and allows other transactions to sneak in between batches.
  2. Indexing: Ensure that the columns used in the JOIN (usually the Primary Key) are indexed on both tables. A missing index on a million-row update can turn a 5-second task into a 5-hour nightmare.
  3. Disable Triggers: If the target table has audit triggers, consider disabling them if you are doing a massive one-time migration to speed up the process—just ensure you document the change for your compliance team.

Conclusion:

Updating a table from another table is a routine task, but it requires atention. Whether you choose the readability of the JOIN syntax or the power of the MERGE statement, the goal is always the same: Absolute Accuracy.

You may also like the following articles: