SQL
Update Records in a Table Using Values From Another Table
Learn to efficiently update column values in a target table by referencing and joining data from a different source table in your SQL database.
-- SQL Server / PostgreSQL
UPDATE
target_table
SET
target_table.column_to_update = source_table.new_value,
target_table.another_column = source_table.another_new_value
FROM
target_table
JOIN
source_table ON target_table.id = source_table.target_id
WHERE
target_table.status = 'pending'; -- Optional condition
-- MySQL
-- UPDATE
-- target_table tt
-- JOIN
-- source_table st ON tt.id = st.target_id
-- SET
-- tt.column_to_update = st.new_value,
-- tt.another_column = st.another_new_value
-- WHERE
-- tt.status = 'pending';
How it works: This query demonstrates how to update records in `target_table` by joining it with `source_table` and using values from the `source_table`. This is highly efficient for batch updates where the new values are derived from a related table. For example, updating a product's price in a `products` table based on a `price_updates` table. The `WHERE` clause can be used to apply the update only to specific records in the `target_table` based on additional conditions.