mariadb-corporation INSERT in MariaDB
Last updated: 2026-06-24
This skill covers the delta between standard SQL INSERT and MariaDB's. It assumes the agent already knows the standard forms (INSERT INTO t (cols) VALUES (…) and INSERT INTO t SELECT …). Everything below documents MariaDB-specific syntax, duplicate-key handling, the RETURNING extension, 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 |
|---|---|
INSERT … then a separate SELECT (or LAST_INSERT_ID() gymnastics) to see what was inserted |
INSERT INTO t (…) VALUES (…) RETURNING id, created_at, … returns the inserted rows in one statement. Supports expressions, stored functions, and single-row subqueries (no aggregates) |
To do an "upsert," reach for REPLACE |
Use INSERT … ON DUPLICATE KEY UPDATE — it updates the existing row in place, preserving unmentioned columns and not churning AUTO_INCREMENT or firing DELETE triggers. REPLACE deletes and re-inserts (see mariadb-replace) |
Inside ON DUPLICATE KEY UPDATE, only knows VALUES(col) to reference the would-be-inserted value |
Both work; VALUE(col) is the current canonical name, VALUES(col) the older alias. Note VALUES(col)/VALUE(col) is only valid inside ON DUPLICATE KEY UPDATE — a syntax error elsewhere |
Expects INSERT IGNORE to skip duplicates silently |
Each skipped row raises a warning (e.g. code 1062) — check SHOW WARNINGS. The fully-silent legacy behavior is gated behind OLD_MODE=NO_DUP_KEY_WARNINGS_WITH_IGNORE, which is deprecated since 11.3 |
Thinks INSERT IGNORE only swallows duplicate-key errors |
It downgrades all errors to warnings — a missing NOT NULL default (1364), out-of-range/invalid values (coerced to the closest valid value), etc. Easy to mask real data problems with it |
Combines INSERT IGNORE … ON DUPLICATE KEY UPDATE (or DELAYED … ON DUPLICATE KEY UPDATE) expecting both to apply |
IGNORE and DELAYED are silently ignored when ON DUPLICATE KEY UPDATE is present |
Reads "2 rows affected" from ON DUPLICATE KEY UPDATE as 2 rows inserted |
The count is 1 per row inserted, 2 per existing row updated (0 if an update changed nothing). So a batch reporting "2" inserted one of nothing — it updated one |
Expects LAST_INSERT_ID() after a multi-row INSERT to give the last generated id |
It returns the AUTO_INCREMENT value of the first successfully inserted row. Derive the others by offset, or use RETURNING id |
Uses INSERT DELAYED as fire-and-forget on any table |
DELAYED is honored only on table-lock engines (MyISAM, Aria, MEMORY, ARCHIVE, BLACKHOLE) and errors (1616) elsewhere; it's also a no-op on replicas, in stored programs, and on partitioned tables. Treat it as legacy — prefer a normal INSERT (and LAST_INSERT_ID() isn't available after a DELAYED insert) |
INSERT … SELECT reading from the same table it inserts into |
Not allowed — the SELECT source can't be the INTO target. Stage through a temporary table |
Adds LOW_PRIORITY / HIGH_PRIORITY to speed up an InnoDB insert |
These only affect table-lock engines; no effect on InnoDB. HIGH_PRIORITY isn't even valid in the INSERT … SELECT form |
| Assumes partition targeting isn't available | INSERT INTO t PARTITION (p0, p1) VALUES (…) restricts the insert to named partitions (errors if a row doesn't belong) |
RETURNING
Return data from the rows just inserted, in a single statement:
INSERT INTO orders (cust_id, total) VALUES (42, 99.50), (43, 12.00)
RETURNING id, cust_id, total * 1.2 AS gross;
Allowed: column references, expressions, stored functions, single-row/single-column subqueries, and prepared statements. Not allowed: aggregate functions. For a plain count, use ROW_COUNT(). RETURNING is a MariaDB extension — not in standard SQL.
ON DUPLICATE KEY UPDATE (the real upsert)
INSERT INTO inventory (sku, qty) VALUES ('A1', 5), ('B2', 3)
ON DUPLICATE KEY UPDATE qty = qty + VALUE(qty);
- Fires when an inserted row would collide with a
PRIMARY KEYorUNIQUEindex. TheUPDATEclause runs against the existing row; unmentioned columns are left untouched. VALUE(col)(aliasVALUES(col)) inside theUPDATEclause = the value that would have been inserted for that column. Bare column names refer to the existing row.- If a row matches more than one unique index, only the first matched index's row is updated — ambiguous on multi-unique tables; design around it.
This is almost always what's wanted over REPLACE. See mariadb-replace for the contrast.
INSERT IGNORE
INSERT IGNORE INTO t (id, name) VALUES (1, 'a'), (1, 'dup'), (2, 'b');
SHOW WARNINGS; -- the skipped duplicate surfaces here
Downgrades errors to warnings rather than aborting the statement. Useful for best-effort bulk loads, but it masks duplicate-key collisions, missing defaults, and value coercion alike — don't use it to paper over a schema mismatch.
Forms and modifiers
-- SET form (one row, named assignments):
INSERT INTO person SET first_name = 'John', last_name = 'Doe';
-- Multi-row VALUES (VALUES and VALUE are interchangeable):
INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c');
-- INSERT ... SELECT (source table ≠ target table):
INSERT INTO archive (id, payload) SELECT id, payload FROM live WHERE ts < '2025-01-01';
-- Partition targeting:
INSERT INTO t PARTITION (p0, p1) VALUES (…);
Priority/queue modifiers: [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] on the VALUES/SET forms; the SELECT form takes only [LOW_PRIORITY | HIGH_PRIORITY] (no DELAYED). All are table-lock-engine concerns — typically irrelevant on InnoDB.
See Also
mariadb-replace—REPLACE(delete-then-insert) and exactly whenON DUPLICATE KEY UPDATEis the better upsertmariadb-select— theSELECTsource forINSERT … SELECT, plusPARTITIONpruning and priority semanticsmariadb-create-table/mariadb-alter-table—AUTO_INCREMENT,DEFAULTexpressions, andUNIQUE/PRIMARY KEYdefinitions that driveON DUPLICATE KEY UPDATEandIGNOREbehavior- Canonical references on
mariadb.com/docs(consult only for edge cases not covered here):- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-on-duplicate-key-update
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insertreturning
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-ignore
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-delayed
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-select
- https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-default-duplicate-values
This page is: Copyright © 2026 MariaDB. All rights reserved.