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

mariadb-replace

Guides writing and reviewing MariaDB REPLACE statements with correct upsert semantics.

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

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? REPLACE in MariaDB is widely misunderstood as an upsert, but it actually deletes the conflicting row and inserts a new one, causing silent data loss, burned AUTO_INCREMENT ids, and unexpected trigger and cascade behavior. This Skill prevents those bugs when writing, generating, or reviewing REPLACE statements. ## Core Features & Use Cases - Delete-then-insert semantics: Explains why REPLACE resets unlisted columns to DEFAULT, generates new AUTO_INCREMENT values, and fires DELETE plus INSERT triggers and ON DELETE cascades. - Upsert contrast: Shows when to use INSERT … ON DUPLICATE KEY UPDATE instead of REPLACE for true insert-or-update behavior. - Full syntax coverage: Documents the VALUES, SET, and SELECT forms, PARTITION targeting, REPLACE … RETURNING, and the INSERT+DELETE privilege requirement. - Use Case: When asked to write an upsert for a users table, the Skill steers you to ON DUPLICATE KEY UPDATE so existing columns and ids are preserved instead of silently wiped. ## Quick Start Ask the AI to write or review a MariaDB REPLACE statement for your table and confirm whether an upsert with ON DUPLICATE KEY UPDATE is the safer choice.

Frequently Asked Questions about mariadb-replace

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 for an upsert in MariaDB, not REPLACE. REPLACE deletes the conflicting row and inserts a new one, resetting unlisted columns to DEFAULT, while ON DUPLICATE KEY UPDATE modifies only the columns you specify in place.

What is the difference between REPLACE and ON DUPLICATE KEY UPDATE?▼

REPLACE deletes the existing row and inserts a fresh one, firing DELETE and INSERT triggers and ON DELETE cascades. ON DUPLICATE KEY UPDATE keeps the original row and updates only listed columns, preserving the id and other column values.

When does MariaDB REPLACE actually replace a row?▼

REPLACE acts only on PRIMARY KEY or UNIQUE index conflicts. Without such an index it behaves as a plain INSERT, and if a row conflicts on several unique keys, all matching rows are deleted before the insert.

Why does REPLACE reset columns I did not specify?▼

REPLACE inserts a brand-new row after deleting the old one, so any column not listed is set to its DEFAULT value rather than keeping the previous value. This is the most damaging REPLACE footgun for partial updates.

What privileges does REPLACE require in MariaDB?▼

REPLACE requires both INSERT and DELETE privileges on the table because it internally deletes the conflicting row before inserting. Granting only INSERT will cause the statement to fail for users running REPLACE.

Does MariaDB REPLACE support a RETURNING clause?▼

Yes, REPLACE … RETURNING returns the newly inserted rows and supports expressions, aliases, stored functions, and single-value subqueries. Aggregates and multi-row subqueries are not allowed; use ROW_COUNT() for a count.

Related Skills

Explore More Software Engineering Skills→
zomeruzomeru

drizzle

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

Community
Intermediate
sebauvraysebauvray

db-schema

Documents the exact MariaDB schema of the Toollab Laravel application including tables, constraints, and known inconsistencies.

Community
Intermediate
BruceTyndallBruceTyndall

deduplication-logic-checker

Validate deduplication guards in rss-to-signals and feed-orchestrator ingestion paths.

Community
Intermediate
LuminaVaultLuminaVault

golang-database

Guides writing and reviewing Go database code using sqlx, pgx, and database/sql.

Official
Advanced