Introduction

MySQL lets you update rows based on joined tables, and that is often what people mean when they ask about UPDATE ... LEFT JOIN. The important distinction is whether you are updating one target table using data from a joined table, or updating multiple joined tables in the same statement. MySQL supports both, but the join shape and SET logic need to be explicit.

Updating One Table Based on a LEFT JOIN

A very common pattern is: keep every row from the left table, join optional data from the right table, and update the left table based on whether a match exists.

1UPDATE customers c
2LEFT JOIN orders o
3  ON o.customer_id = c.id
4  AND o.status = 'PAID'
5SET c.has_paid_order = CASE
6    WHEN o.id IS NULL THEN 0
7    ELSE 1
8END;

Here is what happens:

• every row from customers is considered
• matching paid orders are joined when present
• the CASE expression turns the presence or absence of a match into the new column value

The LEFT JOIN matters because customers with no matching order still stay in the update set.

Updating More Than One Table at Once

MySQL can also update columns in multiple joined tables in a single statement.

1UPDATE orders o
2LEFT JOIN shipments s ON s.order_id = o.id
3SET o.last_checked_at = NOW(),
4    s.last_checked_at = NOW()
5WHERE o.status = 'SHIPPED';

This updates both orders and shipments for the joined rows.

That power is useful, but it also increases risk. When more than one table is being modified, you should be extra careful about the join cardinality and the WHERE clause.

Why LEFT JOIN Can Surprise You

A LEFT JOIN does not magically create a row in the right table. If there is no match, right-side columns are NULL. That means a statement like this:

UPDATE customers c
LEFT JOIN orders o ON o.customer_id = c.id
SET o.status = 'ARCHIVED';

only affects rows where orders actually exists. The join still keeps every customer row logically, but there is nothing to update on the right side when the match is missing.

So LEFT JOIN is most useful when:

• the left table must remain in scope even with missing matches
• the SET clause uses CASE, IFNULL, or COALESCE style logic
• you want to derive flags or summary columns from optional related rows

Preview the Rows First

Before running a joined update, run the same join as a SELECT.

1SELECT c.id, c.has_paid_order, o.id AS order_id, o.status
2FROM customers c
3LEFT JOIN orders o
4  ON o.customer_id = c.id
5  AND o.status = 'PAID';

This is the safest habit for debugging. If the preview result is wrong, the update will be wrong too.

Watch Out for Duplicate Matches

A joined update becomes dangerous when one row in the left table matches several rows on the right. You may think you are applying one simple update, but the join may produce multiple candidate matches.

If your logic expects one related row, enforce that assumption with:

• stricter join conditions
• aggregation in a subquery
• uniqueness constraints in the schema

Without that, the result can be confusing or unintuitive.

Common Pitfalls

The most common mistake is forgetting to preview the join with a SELECT before running the UPDATE.

Another mistake is assuming LEFT JOIN means missing right-side rows can still be updated. They cannot.

A third pitfall is ignoring duplicate matches in the joined table, which can make the update behave differently than expected.

Summary

• Use UPDATE ... LEFT JOIN when the update logic depends on optional related rows.
• A LEFT JOIN keeps all left-table rows in scope, even when the right side is missing.
• MySQL can update one joined table or multiple joined tables in the same statement.
• Preview the join as a SELECT before executing the update.
• Be careful with duplicate matches and NULL right-side values.