MariaDB / Basic: relational foundations / Lesson 6

Insert, update and delete with a clear boundary

Change a training inventory predictably, distinguish duplicate handling strategies and verify the business effect before a commit.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Write scoped INSERT, UPDATE and DELETE statements.
Distinguish upsert from replacement and idempotency.

Specify the intended change first

A mutation has a precondition, target set and postcondition. For inventory reservation, require enough units, identify the exact item and prove the new balance remains nonnegative. A preliminary SELECT is helpful for human review but does not reserve the row against concurrent writers. Put the condition in UPDATE itself or use a transactional locking pattern. Always name INSERT columns so later schema changes cannot silently reinterpret positional values.

Make the business condition atomic

Assume an isolated InnoDB inventory fixture has item 4 with quantity 9. The conditional UPDATE reserves three units only if sufficient stock remains. ROW_COUNT() immediately after the statement should be one under these assumptions, and the following SELECT should show six. A result of zero means the caller must distinguish missing inventory from insufficient stock. Do not overwrite the balance with a number calculated from an earlier unlocked application read.

sql Illustrative SQL; expected results are reasoned, not measured.

START TRANSACTION;
UPDATE inventory SET quantity = quantity - 3
WHERE item_id = 4 AND quantity >= 3;
SELECT ROW_COUNT() AS changed;
SELECT quantity FROM inventory WHERE item_id = 4;
ROLLBACK;

Expected output

Expected with quantity initially 9: changed = 1; quantity = 6; rollback restores 9.

ROLLBACK makes this a rehearsal pattern for transactional data; no execution is claimed.

Duplicate handling changes semantics

INSERT ... ON DUPLICATE KEY UPDATE can update an existing row when a unique or primary key conflicts. Know which unique key can trigger it; multiple competing unique constraints make assumptions dangerous. REPLACE has delete-then-insert semantics when a key conflicts, so it can activate cascades, defaults and triggers differently from UPDATE. INSERT IGNORE can downgrade some errors to warnings. None of these constructs automatically gives a network-retried business request an idempotency contract.

Update a stable reference row

This lab table has only one unique identity, the station code. The second insert changes its display name through the duplicate-key branch. The expected final row is R1 with Riverside Depot. VALUES(name) in this MariaDB construct refers to the candidate inserted value; do not import version-specific MySQL advice about different row-alias syntax without checking MariaDB support. For counters, decide whether a retry should add again or be recognized through a separate request key.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TABLE stations (code VARCHAR(8) PRIMARY KEY, name VARCHAR(80))
ENGINE=InnoDB;
INSERT INTO stations (code, name) VALUES ('R1', 'River Depot');
INSERT INTO stations (code, name) VALUES ('R1', 'Riverside Depot')
ON DUPLICATE KEY UPDATE name = VALUES(name);
SELECT code, name FROM stations ORDER BY code;

Expected output

code | name
R1   | Riverside Depot

The existing row receives a new name; this is not a delete-and-insert replacement.

Verification includes more than affected rows

DELETE removes matching rows; TRUNCATE is DDL with different locking, foreign-key and implicit-commit behavior, so it is not a transactional shortcut for an ordinary delete. Preview the exact WHERE scope and retain recovery evidence appropriate to the operation. Affected-row reporting can depend on whether values changed and connector flags, especially for upserts. Verify the expected final state and related rows, not only a client message. Inspect warnings immediately when a statement permits coercion.

Common errors and fixes

Every inventory row changes

Why it happens: An UPDATE omitted or weakened the intended WHERE predicate.
How to fix it: Stop before commit when possible and use the reviewed recovery path.
Prevent it next time: Review target keys and expected row counts in a lab transaction.

A reservation silently oversells

Why it happens: The application computed a new quantity from a stale read.
How to fix it: Use a conditional arithmetic update and handle a zero-row outcome.
Prevent it next time: Keep the stock precondition inside the atomic write.

Related rows disappear after REPLACE

Why it happens: Replacement deleted the old parent and triggered delete consequences.
How to fix it: Use an explicit UPDATE or carefully designed upsert where identity must persist.
Prevent it next time: Review triggers and referential actions before selecting replacement syntax.

Check your understanding

1. Inventory has 2 units and a request needs 3. What does the conditional reservation do?

Show worked solution

The quantity >= 3 predicate is false, so the row remains at 2 and no reservation succeeds. Return an insufficient-stock outcome after distinguishing a missing item if the API needs that distinction. Do not retry by removing the predicate. A subsequent valid request for one unit may succeed through the same atomic pattern.

2. A payment request may be retransmitted after a lost response. Explain why adding its amount with an upsert is insufficient.