SQL UPDATE with JOIN for Multi-Table Updates

SQL UPDATE with JOIN for Multi-Table Updates

Updating rows based on data from another table is a common requirement. The syntax varies between database systems, but the concept remains the same.

PostgreSQL Syntax

UPDATE orders o
SET status = 'vip_order',
    discount = 0.15
FROM customers c
WHERE o.customer_id = c.id
  AND c.tier = 'VIP';

PostgreSQL uses the FROM clause to join additional tables.

MySQL Syntax

UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.status = 'vip_order',
    o.discount = 0.15
WHERE c.tier = 'VIP';

MySQL places the JOIN directly in the UPDATE statement.

SQL Server Syntax

UPDATE o
SET o.status = 'vip_order',
    o.discount = 0.15
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.tier = 'VIP';

Update with Subquery (Standard SQL)

UPDATE orders
SET discount = 0.15
WHERE customer_id IN (
  SELECT id FROM customers WHERE tier = 'VIP'
);

This approach works across all database systems but may be slower for complex conditions.

Updating with Aggregated Values

UPDATE products p
SET avg_rating = sub.avg_rating
FROM (
  SELECT product_id, AVG(rating) AS avg_rating
  FROM reviews
  GROUP BY product_id
) sub
WHERE p.id = sub.product_id;

Safety Precautions

• Always use a WHERE clause. An UPDATE without WHERE modifies every row in the table
• Test with SELECT first: Replace UPDATE ... SET with SELECT to preview which rows will be affected
• Use transactions: Wrap updates in BEGIN/COMMIT so you can ROLLBACK if something goes wrong
• Check row count: After the update, verify the number of affected rows matches your expectation
• Back up before bulk updates: For critical tables, create a backup or snapshot before mass updates

UPDATE with JOIN is a powerful pattern for synchronizing data across related tables and applying conditional modifications based on external criteria.