How to Update a Table Using Data from Another Table in SQL ?

If you’ve ever needed to update data in one SQL table using values from another, you know how tricky it can seem at first. But SQL makes this process straightforward with the right techniques. Today, we’ll walk through how to update table records by using a SELECT query as part of the process.

Let’s break it down step by step with a real example.

The Scenario

Imagine you have two tables:

DimProducts – This table contains product data, including product prices.
StageProducts – This table contains new or updated prices for some of those products.

Your goal is to update the prices in the DimProducts table with the latest ones found in StageProducts.

For this, you can use an UPDATE query that references a JOIN between these tables.

Writing the Update Query

Updating a table with values from another table is simple when you know the proper SQL syntax. Here's what the query looks like:

UPDATE DimProducts  
SET DimProducts.ProductPrice = StageProducts.ProductPrice  
FROM DimProducts  
INNER JOIN StageProducts  
ON DimProducts.ProductID = StageProducts.ProductID;

Key Points in the Query

UPDATE Clause: This specifies the table to update, which in this case is DimProducts.
SET Clause: This assigns the new value to the ProductPrice column in DimProducts.
FROM Clause: This brings in the second table, StageProducts, which holds the updated prices.
INNER JOIN: This ensures the update only happens where ProductID matches in both tables.

This is efficient because it updates only the relevant records, not the entire table.

What Happens When You Execute

When you run the above query, SQL Server updates only the rows where the ProductID exists in both tables. In our example, three rows were updated.

Verifying the Update

After the query runs, you can confirm the changes by querying the DimProducts table again:

SELECT * FROM DimProducts;

You’ll see that the ProductPrice values for the relevant rows have been updated successfully.

An Alternative: Using the MERGE Statement

Another way to achieve this is by using the MERGE statement, which is slightly more advanced. The MERGE statement can handle updates, inserts, and deletes in a single query. This makes it useful for scenarios where you want more control over how the data changes.

If you’re interested in learning how to use MERGE, check out additional resources or tutorials that outline its full potential.

Why This Matters

Whether you're managing product prices, customer data, or inventory, keeping data up to date is crucial. Knowing how to update one table with data from another is a critical skill for data analysts, database admins, and developers alike.

This approach is practical, efficient, and easy to apply once you know the syntax.

Conclusion

Updating a table using another table’s data might seem intimidating, but SQL makes it straightforward with tools like the UPDATE statement and JOIN. By following the example above, you can confidently handle this process and keep your data accurate and up to date.

If you're ready to take your SQL skills further, consider practicing these techniques online or diving into advanced features like the MERGE statement.

For more tips, tutorials, and SQL tricks, explore interactive courses or certifications to refine your expertise. Keep learning and improving—mastering SQL will always be a valuable skill in data analytics and beyond.