mariadb-delete

name

mariadb-delete

description

MariaDB-specific syntax and behavior for DELETE — RETURNING deleted rows in one statement, both multi-table forms (DELETE … FROM and DELETE FROM … USING) with ORDER BY/LIMIT (since 11.8), single-table ORDER BY/LIMIT/PARTITION/alias/index hints, DELETE HISTORY for system-versioned tables, FOR PORTION OF for application-time periods, QUICK/LOW_PRIORITY/IGNORE modifiers and their engine limits, deleting from a table referenced in its own subquery, and DELETE-vs-TRUNCATE differences. Use when writing, generating, or reviewing DELETE statements that target MariaDB.

DELETE in MariaDB

Last updated: 2026-06-24

This skill covers the delta between standard SQL DELETE and MariaDB's. It assumes the agent already knows the standard form (DELETE FROM t WHERE …). Everything below documents MariaDB-specific syntax, the multi-table forms, temporal-table deletion, 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
SELECT the rows, then DELETE them, in two round trips	DELETE FROM t WHERE … RETURNING id, name returns the deleted rows in one statement. Single-table only; expressions and aliases allowed, no aggregates
"Multi-table DELETE can't take ORDER BY / LIMIT"	It can, since 11.8: DELETE t1.*, t2.* FROM t1 JOIN t2 ON … ORDER BY t1.id DESC LIMIT 3;. (The reference page is stale on this point — the capability is real.)
Knows only DELETE t1 FROM … for multi-table deletes	Both forms are valid: DELETE t1.*[, t2.*] FROM table_refs WHERE … and DELETE FROM t1[, t2] USING table_refs WHERE …. List each table to delete from with tbl.*
Puts RETURNING on a multi-table DELETE	Not allowed — RETURNING is single-table only. Use the single-table form, or ROW_COUNT() for a count
Uses TRUNCATE to empty a table inside a transaction or while holding a table lock	TRUNCATE does an implicit commit and won't run while the session holds a lock — use DELETE FROM t there. TRUNCATE also skips ON DELETE triggers, ignores FK cascades (and errors if the table is FK-referenced), and resets AUTO_INCREMENT; DELETE does none of these
Expects DELETE FROM t (no WHERE) to reset AUTO_INCREMENT	It does not — the counter keeps climbing. Only TRUNCATE TABLE resets it
Avoids referencing the target table in the DELETE's subquery	Allowed: DELETE FROM t1 WHERE c1 IN (SELECT c1 FROM t1 b WHERE b.c2 = 0); — same source and target works. (Note: the failure on this only existed on long-unmaintained versions)
Drops history of a system-versioned table with a plain DELETE	A plain DELETE only moves rows to history — it doesn't remove them. To prune history use DELETE HISTORY FROM t [BEFORE SYSTEM_TIME …] (needs the dedicated DELETE HISTORY privilege). See mariadb-system-versioned-tables
Hand-rolls range deletion on an application-time-period table (read, delete, re-insert the split)	DELETE FROM t FOR PORTION OF period FROM '2024-01-01' TO '2024-07-01' shrinks/splits overlapping rows automatically. Not available in multi-table DELETE
Adds QUICK or LOW_PRIORITY expecting them to help on InnoDB	Both are table-lock-engine (MyISAM/Aria) concerns — QUICK skips index-block merging, LOW_PRIORITY waits for readers. No effect on InnoDB
Assumes single-table DELETE can't take an alias or index hints	DELETE FROM t AS a WHERE a.c = … (alias, since 11.6) and USE/FORCE/IGNORE INDEX (…) (single-table, since 11.8) are both supported

Multi-table DELETE

Two equivalent syntaxes; pick whichever reads better. The table(s) actually deleted from are named before FROM (first form) or right after DELETE FROM (second form); the rest are join references.

-- Form 1 — deleted tables listed before FROM:
DELETE t1.*, t2.* FROM t1 JOIN t2 ON t1.id = t2.t1_id
  WHERE t2.stale = 1
  ORDER BY t1.id DESC LIMIT 100;        -- ORDER BY / LIMIT since 11.8

-- Form 2 — USING clause:
DELETE FROM t1, t2 USING t1 JOIN t2 ON t1.id = t2.t1_id WHERE t2.stale = 1;

RETURNING is not available on either multi-table form.

RETURNING (single-table)

DELETE FROM sessions WHERE expires_at < NOW()
  RETURNING id, user_id, UPPER(token) AS t;

Per-row expressions, stored functions, single-row subqueries, and AS aliases are allowed; aggregates are not.

Single-table form

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
  FROM tbl [PARTITION (p1, p2)] [AS alias]
  [USE INDEX (…) | FORCE INDEX (…) | IGNORE INDEX (…)]   -- since 11.8
  [WHERE condition]
  [ORDER BY …] [LIMIT row_count]
  [RETURNING select_expr [, …]];

ORDER BY … LIMIT lets you delete a bounded batch in a defined order — useful for chunked deletes over a large table.

Temporal tables

• System-versioned tables: plain DELETE moves rows to history rather than removing them. DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2024-01-01' prunes the history itself and requires the DELETE HISTORY privilege. Full semantics (the BEFORE SYSTEM_TIME TIMESTAMP/TRANSACTION distinction, edge cases): see mariadb-system-versioned-tables.
• Application-time-period tables: DELETE FROM t FOR PORTION OF period FROM expr1 TO expr2 deletes only the overlapping slice of each row's period, splitting rows at the boundaries. Not supported in multi-table DELETE.

DELETE vs TRUNCATE

	DELETE FROM t	TRUNCATE TABLE t
Transactional / rollbackable	Yes	No — implicit commit
Fires ON DELETE triggers	Yes	No
FK cascades / can run if FK-referenced	Cascades fire	Errors if the table is FK-referenced
Resets AUTO_INCREMENT	No	Yes
Can run while holding a table lock	Yes	No

Use TRUNCATE only to fast-empty a standalone table outside a transaction.

See Also

• mariadb-system-versioned-tables — DELETE HISTORY semantics, the DELETE HISTORY privilege, BEFORE SYSTEM_TIME TIMESTAMP vs TRANSACTION
• mariadb-select — shared PARTITION, ORDER BY/LIMIT, and subquery surface
• mariadb-update — the sibling write statement; multi-table rules and FOR PORTION OF mirror DELETE's
• 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/delete (note: as of writing, stale on multi-table ORDER BY/LIMIT — tracked in DOCS-6286)
  • https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table