skills.rest
mariadb-corporation
mariadb-corporationOFFICIAL
Explore all skills by this author →
WebsiteGitHub

mariadb-insert

Documents MariaDB-specific INSERT syntax, upsert behavior, and RETURNING semantics.

28|115|Updated Jan 28, 2025
Search Tags:#SQL#database#returning#mariadb#upsert#insert#on duplicate key update
One-click install
npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-insert-mariadb-corporation
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-insert
Source: https://github.com/mariadb-corporation/mariadb-docs/tree/main/agent-skills/granular/statements/mariadb-insert
Command: npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-insert-mariadb-corporation

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing INSERT statements for MariaDB with generic SQL knowledge leads to missed features and subtle bugs: agents reach for REPLACE instead of a true upsert, misread affected-row counts, misuse LAST_INSERT_ID() after multi-row inserts, or expect INSERT IGNORE to fail silently. This Skill documents the exact delta between standard SQL INSERT and MariaDB's implementation so generated or reviewed statements are correct. ## Core Features & Use Cases - RETURNING extension: Return inserted rows, expressions, and stored-function results in a single round trip instead of a follow-up SELECT. - True upsert guidance: Use INSERT … ON DUPLICATE KEY UPDATE with VALUE()/VALUES() instead of REPLACE, preserving unmentioned columns and AUTO_INCREMENT values. - Trap catalog: Covers INSERT IGNORE warning and error-coercion behavior, DELAYED/priority engine limits, PARTITION targeting, the SET form, and INSERT … SELECT same-table restrictions. - Use Case: When asked to write an upsert for an inventory table, produce INSERT INTO inventory (sku, qty) VALUES ('A1', 5) ON DUPLICATE KEY UPDATE qty = qty + VALUE(qty) rather than a destructive REPLACE. ## Quick Start Write a MariaDB INSERT statement that upserts inventory quantities and returns the affected row ids in one statement.

Frequently Asked Questions about mariadb-insert

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I write an upsert in MariaDB?▼

Use INSERT … ON DUPLICATE KEY UPDATE, which updates the existing row in place when a PRIMARY KEY or UNIQUE index collides. Reference the would-be-inserted value with VALUE(col) or its alias VALUES(col); prefer it over REPLACE, which deletes and re-inserts the row.

How to get inserted row ids in one query in MariaDB?▼

Use the RETURNING clause, a MariaDB extension: INSERT INTO t (…) VALUES (…) RETURNING id returns inserted rows in a single statement. It supports expressions and stored functions but not aggregates; use ROW_COUNT() for a plain count.

Does INSERT IGNORE skip duplicates silently in MariaDB?▼

No, each skipped row raises a warning such as error 1062, visible via SHOW WARNINGS. INSERT IGNORE also downgrades all errors to warnings, including missing NOT NULL defaults and out-of-range values that get coerced, so it can mask real data problems.

Why does LAST_INSERT_ID() return the wrong id after a multi-row insert?▼

LAST_INSERT_ID() returns the AUTO_INCREMENT value of the first successfully inserted row, not the last. Derive subsequent ids by offset, or use INSERT … RETURNING id to capture every generated id directly.

Can I use INSERT DELAYED on InnoDB tables in MariaDB?▼

No, DELAYED is honored only on table-lock engines like MyISAM, Aria, MEMORY, ARCHIVE, and BLACKHOLE, and errors with code 1616 elsewhere. It is also a no-op on replicas, in stored programs, and on partitioned tables, so treat it as legacy.

Can INSERT … SELECT read from the same table it inserts into?▼

No, MariaDB does not allow the SELECT source to be the INTO target. Stage the data through a temporary table first, then insert from that temporary table into the target.

Related Skills

Explore More Software Engineering Skills→
zomeruzomeru

drizzle

Enforces Drizzle ORM schema conventions and query patterns for PostgreSQL databases.

Community
Intermediate
evolution-foundationevolution-foundation
520

db-mysql

Execute read-only SQL queries against MySQL or MariaDB databases.

Official
Intermediate
Impertio-StudioImpertio-Studio
2

mariadb-syntax-sql-dml

Generate deterministic MariaDB SQL DML statements for insert, update, delete, and replace operations.

Official
Advanced
PillArmyPillArmy

mysql-loaddata-stmt

Generate and execute MySQL LOAD DATA statements with method chains.

Official
Intermediate