INSERT/REPLACE/INSERT ... ON DUPLICATE KEY, UPDATE, DELETE, and RETURNING Capabilities

Learning outcomes

ServiceHub receives the same business intent through several write paths: create a contact, upsert a contact by email, assign work, correct a row, or remove stale data. The dangerous assumption is that every SQL statement that leaves one row behind has the same side effects. In MariaDB, REPLACE can delete and reinsert a conflicting row, INSERT ... ON DUPLICATE KEY UPDATE updates an existing row instead, and RETURNING support is statement-specific. Those differences matter to triggers, foreign keys, auto-increment values, audit trails, connectors, and replication.

This lesson treats data modification as an observable state transition. Before choosing syntax, identify the row identity, uniqueness rule, intended trigger/FK behavior, expected affected-row contract, and what the application needs back from the server. Then prove the result from tables and audit evidence rather than from the absence of an error.

1. Reset a write lab with visible side effects

The lab uses a small contact table with three audit triggers so that INSERT, UPDATE and DELETE effects are visible. It also includes work-order and stock rows reused by the transaction and locking lessons. Run it only in a disposable local instance because the first statement drops the whole lab database.

DROP DATABASE IF EXISTS servicehub_tx_lab;CREATE DATABASE servicehub_tx_lab;USE servicehub_tx_lab;CREATE TABLE contact_directory (  contact_id BIGINT NOT NULL AUTO_INCREMENT,  email VARCHAR(190) NOT NULL,  display_name VARCHAR(100) NOT NULL,  revision_no INT NOT NULL DEFAULT 1,  PRIMARY KEY (contact_id),  UNIQUE KEY uq_contact_email (email)) ENGINE=InnoDB;CREATE TABLE contact_audit (  audit_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  action_name VARCHAR(10) NOT NULL,  contact_id BIGINT NULL,  email VARCHAR(190) NOT NULL,  observed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;DELIMITER //CREATE TRIGGER contact_ai AFTER INSERT ON contact_directoryFOR EACH ROW BEGIN  INSERT INTO contact_audit(action_name, contact_id, email)  VALUES ('INSERT', NEW.contact_id, NEW.email);END//CREATE TRIGGER contact_au AFTER UPDATE ON contact_directoryFOR EACH ROW BEGIN  INSERT INTO contact_audit(action_name, contact_id, email)  VALUES ('UPDATE', NEW.contact_id, NEW.email);END//CREATE TRIGGER contact_ad AFTER DELETE ON contact_directoryFOR EACH ROW BEGIN  INSERT INTO contact_audit(action_name, contact_id, email)  VALUES ('DELETE', OLD.contact_id, OLD.email);END//DELIMITER ;CREATE TABLE customers (  customer_id BIGINT PRIMARY KEY,  customer_name VARCHAR(100) NOT NULL) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  customer_id BIGINT NOT NULL,  technician_id INT NULL,  status VARCHAR(20) NOT NULL,  priority INT NOT NULL,  estimated_cost DECIMAL(10,2) NOT NULL,  version_no INT NOT NULL DEFAULT 1,  opened_at DATETIME NOT NULL,  CONSTRAINT fk_wo_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),  KEY ix_wo_status_priority (status, priority, work_order_id),  KEY ix_wo_technician (technician_id, work_order_id)) ENGINE=InnoDB;CREATE TABLE parts_stock (  part_id INT PRIMARY KEY,  part_name VARCHAR(80) NOT NULL,  qty_on_hand INT NOT NULL) ENGINE=InnoDB;CREATE TABLE request_dedup (  request_key VARCHAR(80) PRIMARY KEY,  operation_name VARCHAR(40) NOT NULL,  work_order_id BIGINT NULL,  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO customers VALUES (1,'Northwind Clinic'),(2,'Harbor Labs');INSERT INTO work_orders VALUES (1001,1,101,'open',1,120.00,1,'2026-08-20 08:00:00'), (1002,1,102,'open',2,180.00,1,'2026-08-20 08:10:00'), (1003,2,NULL,'queued',1,90.00,1,'2026-08-20 08:20:00'), (1004,2,103,'closed',3,260.00,1,'2026-08-20 08:30:00');INSERT INTO parts_stock VALUES (1,'Filter cartridge',10),(2,'Control relay',10),(3,'Pressure sensor',8);

USE servicehub_tx_lab;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT * FROM parts_stock ORDER BY part_id;SELECT @@autocommit AS autocommit,       @@transaction_isolation AS isolation_level,       @@innodb_snapshot_isolation AS snapshot_isolation;

On the 12.3.2 course baseline, autocommit is normally enabled and InnoDB uses REPEATABLE READ by default. Record the actual values returned by your instance. Package defaults and per-session changes can differ; production code should inspect the effective contract rather than infer it from a course screenshot.

2. INSERT creates rows; multi-row INSERT is still one statement

An ordinary INSERT attempts to create a row. If a unique or primary-key constraint rejects it, the statement fails unless a deliberate duplicate-handling form is used. Multi-row INSERT sends several candidate rows in one statement; that reduces round trips, but it does not remove constraint semantics. The application should still know whether it needs all-or-nothing behavior, IGNORE-style error suppression, or an explicit upsert rule.

INSERT INTO contact_directory(email, display_name)VALUES ('ava@example.test','Ava')RETURNING contact_id, email, display_name, revision_no;INSERT INTO contact_directory(email, display_name)VALUES ('ben@example.test','Ben'),('chen@example.test','Chen')RETURNING contact_id, email;SELECT * FROM contact_audit ORDER BY audit_id;

INSERT ... RETURNING returns the inserted-row expressions in the same round trip, including generated AUTO_INCREMENT values and defaults. That is stronger than issuing a separate SELECT that might need another key or another round trip. It is still a result set: connectors must fetch it correctly instead of assuming every write returns only an affected-row count.

3. REPLACE is not an update

MariaDB documents REPLACE as INSERT-like syntax that first deletes conflicting row(s) identified by PRIMARY KEY or UNIQUE indexes and then inserts the replacement. That means a replacement can allocate a new AUTO_INCREMENT value, execute DELETE and INSERT triggers, and invoke ON DELETE foreign-key actions. If more than one unique key conflicts, multiple old rows can be deleted. Treating this as “UPDATE but shorter” is a correctness bug.

SELECT contact_id, email, display_name FROM contact_directoryWHERE email='ava@example.test';REPLACE INTO contact_directory(email, display_name)VALUES ('ava@example.test','Ava Renamed')RETURNING contact_id, email, display_name;SELECT contact_id, email, display_name FROM contact_directoryWHERE email='ava@example.test';SELECT action_name, contact_id, emailFROM contact_auditWHERE email='ava@example.test'ORDER BY audit_id;

You should see an initial INSERT audit record and, for the replacement, DELETE plus INSERT records. The new contact_id can differ from the original because a new AUTO_INCREMENT value is generated. That observable identity change is why REPLACE can break application assumptions even when the email and final display name look correct.

4. ON DUPLICATE KEY UPDATE expresses an upsert

INSERT ... ON DUPLICATE KEY UPDATE attempts the insert and, when a duplicate unique key is found, updates the existing row rather than deleting it. That preserves the row identity in the common single-key case and invokes UPDATE semantics. It is usually the better fit for “create if absent, otherwise revise this same entity.”

INSERT INTO contact_directory(email, display_name)VALUES ('ben@example.test','Ben Revised')ON DUPLICATE KEY UPDATE  display_name = VALUES(display_name),  revision_no = revision_no + 1RETURNING contact_id, email, display_name, revision_no;SELECT action_name, contact_id, emailFROM contact_auditWHERE email='ben@example.test'ORDER BY audit_id;

The audit trail should show UPDATE rather than DELETE plus INSERT, and the contact identity remains the same. MariaDB warns that when a table has multiple unique indexes and a candidate row conflicts with more than one, only the first matched unique index is updated; designing ambiguous multi-unique “upserts” is therefore risky. Prefer a single business key for the operation and test duplicate behavior explicitly.

5. UPDATE and DELETE need scope guardrails

UPDATE and DELETE can affect every matching row, and omitting WHERE can affect the entire table. A safe operational workflow separates target proof from state change: first express the predicate in SELECT form, inspect count/key samples, then execute the write inside the appropriate transaction and verify affected and resulting rows. This is especially important for one-off production repair statements.

SELECT work_order_id, status, priorityFROM work_ordersWHERE status='queued';START TRANSACTION;UPDATE work_ordersSET status='open', version_no=version_no+1WHERE status='queued';SELECT ROW_COUNT() AS rows_updated;SELECT work_order_id, status, version_noFROM work_orders WHERE work_order_id=1003;ROLLBACK;

The transaction lets you test a data change without keeping it. Do not put schema DDL into that same safety pattern: MariaDB DDL commonly causes implicit commit, which Lesson 2 examines. Also remember that an affected-row count does not prove the correct rows changed. Verification must query the intended keys and invariants.

INSERT INTO contact_directory(email, display_name)VALUES ('temporary@example.test','Temporary') RETURNING contact_id;DELETE FROM contact_directoryWHERE email='temporary@example.test'RETURNING contact_id, email, display_name;

On current MariaDB, single-table DELETE can return expressions from deleted rows. Multi-table DELETE has a different syntax and does not share every RETURNING capability. For UPDATE, use the documented syntax for the exact target version and obtain post-update state through an explicit SELECT or application transaction pattern rather than assuming RETURNING exists.

6. Failure drill and acceptance checklist

The deliberate mistake is to use REPLACE as an idempotent retry mechanism for an entity whose identity must remain stable. Execute the Ava example, inspect the trigger audit, and explain why a repeated request can allocate a new identity and activate delete side effects. Repair it with a uniqueness-aware INSERT ... ON DUPLICATE KEY UPDATE whose update list is explicit.

1. Run the reset script and record server/autocommit/isolation values.
2. Use INSERT RETURNING and prove generated IDs from the returned rows.
3. Run REPLACE on Ava and prove DELETE + INSERT from contact_audit.
4. Run ON DUPLICATE KEY UPDATE on Ben and prove UPDATE behavior.
5. Execute an UPDATE inside a transaction, verify keys and row count, then ROLLBACK.
6. Use single-table DELETE RETURNING on a disposable row.
7. Write down which statements your connector expects to return result sets versus affected-row counts.

7. Summary and bridge

MariaDB write statements are not interchangeable. INSERT creates, REPLACE may delete then insert, ON DUPLICATE KEY UPDATE expresses an update-style upsert, and UPDATE/DELETE require explicit scope and verification. RETURNING is useful but version- and statement-specific. The reliable pattern is intent → constraint/key model → statement → returned/affected evidence → state verification.

The next lesson groups several writes into one application transaction. You will make autocommit explicit, use START TRANSACTION and SAVEPOINT, observe in_transaction, and deliberately trigger a mid-workflow failure to prove which effects roll back—and which schema statements can commit behind your back.