mariadb-corporation 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
DELETEmoves rows to history rather than removing them.DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2024-01-01'prunes the history itself and requires theDELETE HISTORYprivilege. Full semantics (theBEFORE SYSTEM_TIME TIMESTAMP/TRANSACTIONdistinction, edge cases): seemariadb-system-versioned-tables. - Application-time-period tables:
DELETE FROM t FOR PORTION OF period FROM expr1 TO expr2deletes 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 HISTORYsemantics, theDELETE HISTORYprivilege,BEFORE SYSTEM_TIMETIMESTAMP vs TRANSACTIONmariadb-select— sharedPARTITION,ORDER BY/LIMIT, and subquery surfacemariadb-update— the sibling write statement; multi-table rules andFOR PORTION OFmirror 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
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete (note: as of writing, stale on multi-table
This page is: Copyright © 2026 MariaDB. All rights reserved.