Przejdź do treści
Biblioteka skilli
Programowanie · Nauka i wyjaśnianie

mariadb-update

/mariadb-update

Opisuje składnię i zachowanie UPDATE w MariaDB: formy jedno- i wielotabelowe, ORDER BY z LIMIT, RETURNING z OLD_VALUE(), kolejność przypisań, aktualizację tabel wersjonowanych oraz CTE przed UPDATE.

Repozytorium
ai-plugins ↗
Licencja
LGPL
Ostatnia zmiana repozytorium
24.10.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
2
Przestarzałość

Brak sygnałów. Repozytorium zmienione 24.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

LGPL, otwarta.

Co robi i kiedy

Opisuje składnię i zachowanie UPDATE w MariaDB: formy jedno- i wielotabelowe, ORDER BY z LIMIT, RETURNING z OLD_VALUE(), kolejność przypisań, aktualizację tabel wersjonowanych oraz CTE przed UPDATE.

Oryginalny opis (EN)

MariaDB-specific syntax and behavior for UPDATE — single-table vs multi-table forms and their different rules, UPDATE … ORDER BY … LIMIT, PARTITION pruning, RETURNING with OLD_VALUE() (since 13.0), left-to-right assignment evaluation and SIMULTANEOUS_ASSIGNMENT sql_mode, IGNORE / LOW_PRIORITY modifiers, rows-matched-vs-changed reporting, updating system-versioned and application-time-period (FOR PORTION OF) tables, and CTEs before UPDATE (since 12.3). Use when writing, generating, or reviewing UPDATE statements that target MariaDB.

Jak działa

  1. What LLMs Often Miss
  2. Single-table vs multi-table — the core split
  3. Assignment evaluation order
  4. RETURNING (since 13.0)
  5. ORDER BY … LIMIT (single-table)
  6. Temporal tables
  7. See Also
Sekcje z nagłówków SKILL.md, w oryginale · 109 linii, 1 067 słów
Podgląd SKILL.md
---
name: mariadb-update
description: "MariaDB-specific syntax and behavior for UPDATE — single-table vs multi-table forms and their different rules, UPDATE … ORDER BY … LIMIT, PARTITION pruning, RETURNING with OLD_VALUE() (since 13.0), left-to-right assignment evaluation and SIMULTANEOUS_ASSIGNMENT sql_mode, IGNORE / LOW_PRIORITY modifiers, rows-matched-vs-changed reporting, updating system-versioned and application-time-period (FOR PORTION OF) tables, and CTEs before UPDATE (since 12.3). Use when writing, generating, or reviewing UPDATE statements that target MariaDB."
---

# 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

| If the agent writes / assumes… | …prefer the MariaDB form |
|---|---|
| Assumes `UPDATE … RETURNING` is unsupported (only `INSERT`/`DELETE` return rows) | `UPDATE t SET a = a + 1 WHERE id = 5 RETURNING OLD_VALUE(a) AS before, a AS after;` — supported **since 13.0**, single-table only. `OLD_VALUE(col)` gives the pre-update value, a bare column gives the new one |
| Adds `ORDER BY` or `LIMIT` to a **multi-table** UPDATE | Not allowed. `ORDER BY` and `LIMIT` work only on the **single-table** form. Same restriction applies to `PARTITION` and `RETURNING` — all four are single-table-only |
| `UPDATE t SET col1 = col2, col2 = col1` expecting a simultaneous swap | Single-table UPDATE evaluates assignments **left-to-right**, so by the time `col2` is set, `col1` already holds the new value (no swap). For simultaneous semantics: `SET sql_mode = 'SIMULTANEOUS_ASSIGNMENT'`. Multi-table UPDATE gives **no** ordering guarantee at all |
| Sets the same column twice in one statement expecting last-write-wins | In plain mode the last assignment wins. Under `SIMULTANEOUS_ASSIGNMENT` it is an **error** (`ER_UPDATED_COLUMN_ONLY_ONCE`) — a column can't be changed more than once per statement |
| Treats the "rows affected" count as rows matched by `WHERE` | UPDATE reports rows **changed**, not matched. Rows whose new value equals the old are matched but not counted as changed (server message: `Rows matched: N Changed: M`). The client-side count depends on the `CLIENT_FOUND_ROWS` connection flag |
| Assumes one UPDATE of a system-versioned table writes one row | Each changed row also **inserts a history row**; the result reports an extra `Inserted: N`. Budget for the storage and for triggers seeing the history write. See `mariadb-system-versioned-tables` |
| Reaches for `LOW_PRIORITY` to de-prioritize an UPDATE on InnoDB | `LOW_PRIORITY` only affects table-lock engines (MyISAM, MEMORY, Aria). On InnoDB (row-level locking) it has **no effect** — drop it from generated SQL |
| Expects `IGNORE` to skip every problem row | `UPDATE IGNORE` **skips** rows that would cause duplicate-key conflicts, but rows with data-conversion problems are **clamped to the closest valid value**, not skipped. It's not a blanket "ignore errors" |
| Avoids referencing the target table in the `UPDATE`'s own `WHERE` subquery | Allowed: `UPDATE t SET c = c + 1 WHERE id = (SELECT MAX(id) FROM t);`. (Note: `DELETE` has the *opposite* restriction — it cannot delete from a table read in a subquery. See `mariadb-delete`) |
| Tries to assign to a column of a CTE or derived table | CTEs and derived tables in an UPDATE are **read-only** — read from them, but update only a real base table. `WITH … UPDATE …` is supported **since 12.3** |
| Assumes partition selection isn't available | `UPDATE t PARTITION (p0, p1) SET … WHERE …` limits the update to named partitions (single-table form) |

## Single-table vs multi-table — the core split

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

```sql
-- 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:

```sql
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:

```sql
-- 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:

```sql
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:

```sql
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)

<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>

Wersje

W bazie nie ma innych skilli o nazwie mariadb-update.

Identyczna treść występuje jeszcze w 8 innych folderach na GitHubie. Tu pokazujemy jedną, najlepiej ocenioną kopię.

Migawka bazy z 13.11.2026 · wersja paczki b3f8a31a7bc4 (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100mariadb-delete
    Opisuje składnię i zachowanie DELETE w MariaDB: RETURNING usuniętych wierszy, formy wielotabelowe z ORDER BY i LIMIT, DELETE HISTORY dla tabel wersjonowanych oraz FOR PORTION OF dla okresów.
  • 100mariadb-insert
    Opisuje składnię i zachowanie INSERT w MariaDB: RETURNING, ON DUPLICATE KEY UPDATE, INSERT IGNORE, INSERT SELECT, wstawianie do partycji oraz semantykę LAST_INSERT_ID() po wstawieniu wielu wierszy.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.