MariaDB DML : INSERT, UPDATE, DELETE, REPLACE

Deterministic SQL DML for MariaDB. Use these patterns to write correct INSERT, UPDATE, DELETE, and REPLACE statements, and to avoid the well-known footguns that look like they work and silently corrupt data.

Quick Reference

• ALWAYS prefer INSERT ... ON DUPLICATE KEY UPDATE over REPLACE INTO. REPLACE is a DELETE-then-INSERT under the hood, which fires ON DELETE CASCADE, fires DELETE + INSERT triggers, and burns a fresh AUTO_INCREMENT value on every call (verified : KB replace/).
• NEVER use INSERT IGNORE to mean "skip if duplicate". It silently coerces NOT NULL violations to empty string / zero, suppresses out-of-range conversion errors, and hides every other constraint violation as a warning. Use INSERT ... ON DUPLICATE KEY UPDATE with a no-op SET, or a pre-check SELECT.
• ON DUPLICATE KEY UPDATE burns AUTO_INCREMENT when the UPDATE branch is taken on tables with non-zero auto_increment_increment. Each conflicting INSERT consumes a sequence value even though no row is inserted ; expect gaps. LAST_INSERT_ID() returns the new auto-increment value only when the INSERT branch was taken ; on the UPDATE branch it returns the existing row's id only if you write id = LAST_INSERT_ID(id) in the SET list (idiom from KB).
• ON DUPLICATE KEY UPDATE with multiple UNIQUE indexes is UNDEFINED. KB verbatim : "If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index."
• UPDATE RETURNING does NOT exist in 10.6-LTS, 10.11-LTS, 11.x, or 12.x. RETURNING is supported on INSERT (since 10.5), DELETE (since 10.0), and REPLACE (since 10.5.0). UPDATE RETURNING is only available from MariaDB 13.0. Verified : KB update/ documents RETURNING as 13.0-only. Do NOT assume MySQL or PostgreSQL parity.
• Multi-table UPDATE / DELETE row order is undefined. Do not write logic that depends on which row is updated first. If order matters, use a single-table statement with ORDER BY ... LIMIT and repeat in a loop, or wrap a deterministic subquery in a transaction.
• DELETE HISTORY purges row-history on system-versioned tables ; regular DELETE creates history. They are NOT interchangeable.
• RETURNING returns affected-row data in a single round-trip, replacing the older INSERT ; SELECT LAST_INSERT_ID() pattern. Use it whenever the inserted / deleted row data is needed downstream.
• UPDATE without WHERE updates every row. Use --safe-updates mode in interactive sessions, or always include a primary-key predicate.

Decision Trees

Tree 1 : "I want an upsert"