Mariadb Update

UPDATE in MariaDB

Last updated: 2026-06-24

This skill covers the delta between standard SQL UPDATE and MariaDB's. It assumes the agent already knows the standard form (UPDATE t SET col = expr WHERE …). Everything below documents MariaDB-specific syntax, assignment semantics, the single-table/multi-table split, and the most common LLM traps.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Single-table vs multi-table — the core split

The two forms have different grammars. Mixing them up is the most common structural error.

-- Single-table: supports PARTITION, ORDER BY, LIMIT, RETURNING, FOR PORTION OF
UPDATE [LOW_PRIORITY] [IGNORE] table_ref
    [PARTITION (p_list)]
    [FOR PORTION OF period FROM expr1 TO expr2]
    SET col1 = {expr | DEFAULT} [, col2 = {expr | DEFAULT}] …
    [WHERE condition]
    [ORDER BY …]
    [LIMIT row_count]
    [RETURNING select_expr [, …]];        -- since 13.0

-- Multi-table: NONE of PARTITION / ORDER BY / LIMIT / RETURNING
UPDATE [LOW_PRIORITY] [IGNORE] table_refs
    SET col1 = {expr | DEFAULT} [, …]
    [WHERE condition];

A multi-table UPDATE joins tables and can write to several of them in one statement:

UPDATE orders o JOIN customers c ON o.cust_id = c.id
  SET o.region = c.region, c.order_count = c.order_count + 1
  WHERE o.status = 'new';

Assignment evaluation order

Single-table UPDATE assigns left-to-right by default, so later assignments see the results of earlier ones:

-- counter ends at +2, because col2 sees the already-incremented col1:
UPDATE t SET col1 = col1 + 1, col2 = col1 WHERE id = 1;

-- True simultaneous assignment (e.g. a swap) needs the sql_mode:
SET sql_mode = 'SIMULTANEOUS_ASSIGNMENT';
UPDATE t SET col1 = col2, col2 = col1 WHERE id = 1;   -- swaps

Multi-table UPDATE makes no ordering guarantee — don't rely on assignment order across joined tables.

RETURNING (since 13.0)

Single-table UPDATE can return data from the affected rows. Use OLD_VALUE(col) for the value before the update and a bare column reference for the value after:

UPDATE accounts SET balance = balance - 100
  WHERE id = 42
  RETURNING id, OLD_VALUE(balance) AS old_balance, balance AS new_balance;

RETURNING is not available on multi-table UPDATE (errors with "RETURNING for multi-table UPDATE").

ORDER BY … LIMIT (single-table)

Bound how many rows a single-table UPDATE touches, in a defined order — useful for batched updates over a large table:

UPDATE page_hit SET processed = 1
  WHERE processed = 0
  ORDER BY ts
  LIMIT 1000;

Temporal tables

• System-versioned tables: a normal UPDATE automatically closes the old row version and inserts a history row (reflected in the Inserted: N count). Query history with SELECT … FOR SYSTEM_TIME …. Full model: see mariadb-system-versioned-tables.
• Application-time-period tables: UPDATE t FOR PORTION OF period FROM expr1 TO expr2 SET … updates only the slice of each row's period that overlaps the range, splitting rows at the boundaries as needed.

See Also

• mariadb-system-versioned-tables — history-row semantics, FOR SYSTEM_TIME, versioning-aware behavior referenced above
• mariadb-select — shared ORDER BY, LIMIT, PARTITION, and subquery surface; row-locking semantics that interact with UPDATE
• mariadb-delete — the sibling write statement, with the opposite same-table-subquery restriction
• mariadb-insert — INSERT … ON DUPLICATE KEY UPDATE when you mean "update if present, else insert"
• Canonical references on mariadb.com/docs (consult only for edge cases not covered here):
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/high_priority-and-low_priority (LOW_PRIORITY engine scope)

This page is: Copyright © 2026 MariaDB. All rights reserved.