Update Table From Another Table In Sql

Introduction

Updating one table based on the contents of another table is a powerful technique in SQL that allows developers and analysts to synchronize data, propagate changes, or consolidate information across related tables. Whether you are merging data from a staging table, applying corrections from a reference table, or copying aggregated values, the ability to update a target table using data from a source table is essential for maintaining data integrity and automating data‑loading processes. This article walks you through the concepts, syntax, and best practices for performing an UPDATE that pulls values from another table, helping you write efficient, reliable SQL statements.

Why Update One Table Based on Another?

There are several scenarios where updating a table from another table becomes necessary:

• Data synchronization: A staging table receives new records, and you need to reflect those changes into the production table.
• Correcting or enriching data: A reference table holds corrected values or lookup information that should overwrite existing fields.
• Consolidating aggregates: Summary tables are rebuilt by pulling computed values from detail tables.
• Maintaining historical records: Old data is archived, and a current table is refreshed using the archived version.

Understanding the underlying reasons helps you choose the most appropriate method—whether a simple JOIN, a subquery, or a multi‑table statement That's the part that actually makes a difference..

Common Use Cases

Below are typical situations where you might need to update a table from another table:

1. Apply price changes from a CSV import – Load new prices into a temporary table, then update the Products table.
2. Fix inconsistent states – Use a lookup table to correct misspelled values in the Customers table.
3. Refresh summary statistics – Update a SalesSummary table by aggregating data from the Sales table.
4. Merge duplicate records – Choose the most recent record from a TempRecords table and overwrite the main Employees table.

Each case can be handled with a similar pattern, but the exact SQL will vary based on the relationship between the tables Nothing fancy..

How to Perform the Update

Basic Syntax

The simplest form uses a JOIN between the target table (T) and the source table (S). The generic syntax looks like this:

UPDATE T
SET    T.column1 = S.column1,
       T.column2 = S.column2
FROM   source_table AS S
INNER JOIN target_table AS T
        ON T.id = S.id;

Here, T.Day to day, id = S. id defines the matching condition; only rows where the join succeeds are updated. If you omit the FROM clause (as in MySQL), you can still reference the source table using a subquery That alone is useful..

Using a JOIN

When both tables share a common key, a JOIN is the most readable approach. Take this: suppose you have a Products table that needs price updates from a PriceUpdates table:

UPDATE p
SET    p.list_price = u.new_price,
       p.last_updated = CURRENT_TIMESTAMP
FROM   Products AS p
JOIN   PriceUpdates AS u
       ON p.product_id = u.product_id;

• The JOIN ensures only matching product_id rows are affected.
• CURRENT_TIMESTAMP automatically records when the change occurred.

If you need to update based on multiple source columns, simply add more SET clauses.

Using a Subquery

When the source table is a derived table or a subquery, you can embed the logic directly in the SET clause. This is handy when the source data comes from a calculation or a temporary result set:

UPDATE Products
SET    list_price = (
         SELECT MAX(new_price)
         FROM   PriceUpdates
         WHERE  PriceUpdates.product_id = Products.product_id
       )
WHERE  EXISTS (
         SELECT 1
         FROM   PriceUpdates
         WHERE  PriceUpdates.product_id = Products.product_id
       );

• The subquery returns the latest price for each product.
• The WHERE EXISTS clause prevents unnecessary updates for products without a corresponding source row.

Step‑by‑Step Example

Let’s walk through a realistic scenario: a company stores customer information in Customers and receives monthly address corrections in AddressCorrections. The goal is to update the Customers table with the new addresses where a match exists Most people skip this — try not to..

1. Examine the tables

-- Customers table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100),
    street      VARCHAR(150),
    city        VARCHAR(100),
    zip_code    VARCHAR(20)
);

-- AddressCorrections table
CREATE TABLE AddressCorrections (
    customer_id INT PRIMARY KEY,
    street      VARCHAR(150),
    city        VARCHAR(100),
    zip_code    VARCHAR(20)
);

2. Write the UPDATE statement

UPDATE c
SET    c.street   = ac.street,
       c.city     = ac.city,
       c.zip_code = ac.zip_code
FROM   Customers AS c
JOIN   AddressCorrections AS ac
       ON c.customer_id = ac.customer_id;

3. Verify the impact

SELECT COUNT(*) AS updated_rows
FROM   Customers AS c
JOIN   AddressCorrections AS ac
       ON c.customer_id = ac.customer_id;

This count tells you how many rows were potentially changed. You can also preview the changes with a SELECT:

SELECT c.customer_id,
       c.name,
       c.street   AS old_street,
       ac.street  AS new_street,
       c.city     AS old_city,
       ac.city    AS new_city,
       c.zip_code AS old_zip,
       ac.zip_code AS new_zip
FROM   Customers AS c
JOIN   AddressCorrections AS ac
       ON c.customer_id = ac.customer_id;

4. Apply the update

Run the UPDATE statement. If you are using a database that supports transactions (e.g The details matter here. But it adds up..

BEGIN TRANSACTION;

UPDATE c
SET    c.city     = ac.street   = ac.city,
       c.Plus, street,
       c. zip_code = ac.And zip_code
FROM   Customers AS c
JOIN   AddressCorrections AS ac
       ON c. customer_id = ac.

-- Review the changes
SELECT * FROM Customers WHERE customer_id IN (SELECT customer_id FROM AddressCorrections);

COMMIT;   -- or ROLLBACK if something looks wrong

Best Practices and Tips

Transaction Management

• Wrap bulk updates in a transaction to ensure atomicity. If something goes wrong, you can roll back without leaving the database in a partially updated state.
• Use SAVEPOINTS if you need to isolate sub‑operations within a larger transaction.

Indexes and Performance

• Ensure the join columns (customer_id in the example) are indexed in both tables. This dramatically speeds up the matching