Update one table with data from another

sql

join

best-practices

performance

byAlex Kataev·Nov 29, 2024

To update a table with data from another, use a SQL JOIN as shown below:

UPDATE t1
SET t1.col = t2.col
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;

Remember to replace t1, t2, col, table1, table2, id with your actual table and column names. The JOIN connects records across tables via a common identifier, typically using a primary key (id).

If your dataset is large, test the query's speed. For ids that don't match, decide whether you want to skip these records or handle them in some way. Always backup your data before you start updating to prevent unwanted data loss.

SQL Update: In-depth Analysis

Matching data types: Essential, not optional

When updating a table from another, data types must match. Incompatible types can lead to failed updates or unexpected data transformations. Always verify your schema to avoid these scenarios.

Field mapping: The key to accurate updates

Incorrect field mapping can result in faulty updates or loss of data integrity. Ensure your SET clause correctly aligns columns in both tables, especially if the tables have similarly named columns storing different data 👀.

Query syntax: Know your database

SQL syntax varies slightly across different database systems. Some dialects, such as MySQL, do not use the FROM clause in UPDATE statements. Be sure to check the query according to your specific DBMS.

Sample data testing: Because nobody's perfect

Before applying changes to the entire dataset, test your update query on a subset of data. This allows you to catch any errors and confirm the update works as intended.

Advanced Updates

Update via subquery

In some cases, a JOIN might not work or be desired. This is where a subquery can come to the rescue:

UPDATE table1
SET col = (
  SELECT col
  FROM table2
  WHERE table2.id = table1.id
)
WHERE EXISTS (
  SELECT 1
  FROM table2
  WHERE table2.id = table1.id
);

Useful for updates based on multiple conditions, or when you need to manipulate data before updating.

If MERGE is more your style

In SQL Server, the MERGE statement can handle both updates and inserts from one table into another based on certain conditions:

MERGE INTO table1 AS t1
USING table2 AS t2
ON t1.id = t2.id
WHEN MATCHED THEN
  UPDATE SET t1.col = t2.col
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, col) VALUES (t2.id, t2.col);

It's like buying one, getting one free. Or in SQL world, updating one, inserting one free.

Master the Update: Pro Tips

Performance Tuning: Get Fast or Go Home

Working with large-scale data? Index the columns used in the JOIN condition. This optimises the query, making your updates faster than a speeding bullet (almost).

Complex Conditions: Because Life Isn't Always Simple

Sometimes you need to update fields based on more complex conditions:

UPDATE t1
SET t1.col = CASE
                WHEN t2.condition_col > 100 THEN 'High' -- Insider tip: Always aim high!
                WHEN t2.condition_col < 10 THEN 'Low' -- But be prepared for lows...
                ELSE 'Medium' -- Middle ground is also fine.
             END
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;

Atomic Updates with Transactions: The Art of Commitment and Rollbacks

For data integrity, encapsulate your update in a TRANSACTION. So if things go south, you can rollback all changes as if nothing ever happened:

BEGIN TRANSACTION;

UPDATE t1
SET t1.col = t2.col
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;

-- If errors arise, rollback: It's as if it never happened. If all is good, commit: Celebrate your new data!
IF @@ERROR != 0
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;