How to UPDATE with JOIN in MySQL (With Examples)

Introduction

MySQL lets you update one table based on matching data from another table using JOIN syntax. This is essential for data synchronization, batch updates, and ETL operations.

UPDATE with INNER JOIN

Update rows in one table using values from a matching row in another table.

-- Update product prices from a price list:
UPDATE products p
INNER JOIN price_updates pu ON p.sku = pu.sku
SET p.price = pu.new_price,
    p.updated_at = NOW();

UPDATE with LEFT JOIN

Update rows and handle cases where the joined table has no match.

-- Set discount flag based on promotions table:
UPDATE products p
LEFT JOIN promotions pr ON p.id = pr.product_id
SET p.discount = CASE
  WHEN pr.id IS NOT NULL THEN pr.discount_pct
  ELSE 0
END;

UPDATE with Subquery

Use a subquery when you need to update based on aggregated data.

-- Update customers with their total order count:
UPDATE customers c
INNER JOIN (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) o ON c.id = o.customer_id
SET c.total_orders = o.order_count;

Multi-Table UPDATE

MySQL allows updating multiple tables in a single statement.

-- Update both tables at once:
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.status = 'archived',
    c.last_order_date = o.created_at
WHERE o.created_at < '2025-01-01';

Conditional UPDATE with JOIN

Apply different updates based on conditions from the joined table.

-- Tiered discount based on customer tier:
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.discount = CASE
  WHEN c.tier = 'gold' THEN 0.20
  WHEN c.tier = 'silver' THEN 0.10
  ELSE 0.05
END
WHERE o.status = 'pending';

Best Practices

• Always test UPDATE JOIN with a SELECT first to verify the matched rows
• Use LIMIT to test on a small batch before running on the full table
• Wrap large updates in a transaction for rollback capability
• Add a WHERE clause to limit the scope of the update
• Use EXPLAIN on the SELECT version to check performance

Generate MySQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct MySQL query instantly.

Frequently Asked Questions