UPDATE

Changing existing rows with SET, the WHERE you must not forget, and updating one table from another via a join.

SyntaxUPDATE [IGNORE] table_reference SET col_name = value, ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

UPDATE modifies columns of rows that already exist. Everything in the statement is safety scaffolding around one fact: with no WHERE, it rewrites every row in the table.

Single-table and multiple-table forms

The single-table form takes SET, an optional WHERE, and optionally ORDER BY and LIMIT — useful for changing rows in bounded chunks. The multiple-table form takes a join in place of the table name and can write to more than one of the joined tables at once, but it accepts neither ORDER BY nor LIMIT.

How assignments are evaluated

Single-table assignments are evaluated left to right, so in SET col1 = col1 + 1, col2 = col1, col2 receives the already incremented value. For multiple-table updates the manual gives no ordering guarantee at all, so never write one assignment that depends on another.

What trips people up

MySQL refuses UPDATE t ... WHERE id IN (SELECT ... FROM t) with error 1093 — you cannot select from the table you are updating in a subquery. The fix is to wrap the subquery as a derived table in the multi-table form, which materialises it first. Separately, the mysql client's --safe-updates option (also the sql_safe_updates session variable) rejects any UPDATE whose WHERE uses no key and that has no LIMIT. Turn it on in your interactive profile.

Example

-- Step 1: run the SELECT first. Same FROM, same WHERE, so it shows exactly
-- which rows the UPDATE will touch and what they currently hold.
SELECT id, status, shipped_at
FROM   orders
WHERE  status = 'packed'
  AND  shipped_at IS NULL
  AND  packed_at < NOW() - INTERVAL 3 DAY;

-- Step 2: same predicate, now destructive.
UPDATE orders
SET    status     = 'shipped',
       shipped_at = NOW()
WHERE  status = 'packed'
  AND  shipped_at IS NULL
  AND  packed_at < NOW() - INTERVAL 3 DAY;

-- Step 3: ROW_COUNT() is the number of rows actually changed by the statement above.
SELECT ROW_COUNT();

When to use it

Flip a batch of records to a new status and stamp the time it happened.
Copy a computed total from a child table onto its parent row.
Backfill a newly added column in fixed-size batches without locking the table for minutes.
Correct a bad value across rows that match a specific, tested predicate.

More examples

Updating one table from another

This is the MySQL spelling of what other dialects write as UPDATE ... FROM. Use it whenever the new value has to be computed from rows in another table — copying a cached count, applying a per-customer discount tier, propagating a corrected value from a lookup table. Rows with no match on the join side are simply left alone, which is usually what you want.

-- Multi-table form: the table_reference is a join, and SET writes to one side of it.
-- Denormalise the current item count onto each order.
UPDATE orders AS o
JOIN (
    SELECT order_id, SUM(qty) AS total_qty
    FROM   order_items
    GROUP  BY order_id
) AS agg ON agg.order_id = o.id
SET    o.item_count = agg.total_qty
WHERE  o.status <> 'cancelled';
-- Note: ORDER BY and LIMIT are not permitted in the multiple-table form.

Working around error 1093

You hit this the first time you try to update rows selected by an aggregate or a self-referencing condition. The derived-table rewrite is the documented workaround: the subquery is evaluated into its own result before the update begins, so MySQL is no longer reading and writing the same table in one pass.

-- Rejected: ERROR 1093 — you cannot select from the table you are updating.
UPDATE items
SET    retail = retail * 0.9
WHERE  id IN (SELECT id FROM items WHERE retail / wholesale >= 1.3);

-- Accepted: the same logic as a multi-table update against a derived table.
-- NO_MERGE stops the optimizer from folding the derived table back into the outer query.
UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id
        FROM   items
        WHERE  retail / wholesale >= 1.3
          AND  quantity < 100) AS discounted
SET    items.retail = items.retail * 0.9
WHERE  items.id = discounted.id;

Chunked updates with ORDER BY and LIMIT

Reach for this during migrations and backfills on a live table. The WHERE has to be self-limiting (here, 'not yet normalised') so that each run picks up where the last stopped. Remember the manual's wording: LIMIT stops once it has found that many rows satisfying the WHERE, whether or not they were actually changed.

-- Backfilling a new column across ten million rows in one statement holds locks
-- for minutes and bloats the undo log. Do it in bounded batches instead.
UPDATE users
SET    email_normalised = LOWER(TRIM(email))
WHERE  email_normalised IS NULL
ORDER  BY id       -- deterministic order, so each batch takes the next slice
LIMIT  5000;       -- LIMIT here is a rows-MATCHED cap, not a rows-changed cap

-- Repeat until this returns 0.
SELECT ROW_COUNT();

Discussion

Be the first to comment on this lesson.