INSERT, UPDATE & DELETE

"It is a capital mistake to theorize before one has data. Insensibly one begins to twist facts to suit theories, instead of theories to suit facts." — Arthur Conan Doyle, A Scandal in Bohemia

For four parts you have been a reader of data — asking questions with SELECT and trusting that rows were already there. This part flips the pen around. Now you put the facts in, change them when the world changes, and take them out when they no longer belong. This is DML — Data Manipulation Language — the three verbs INSERT, UPDATE, and DELETE that every write in every application eventually becomes.

These three statements are deceptively small. You can learn their syntax in an afternoon. But they are also where the sharpest edges live, because unlike a SELECT — which only reads — a write changes the truth. Forget a single WHERE clause on an UPDATE and you don't get a wrong answer; you overwrite every customer in the table and there is no undo button waiting for you. The most quietly terrifying stories senior engineers tell start with the words "I ran an UPDATE and forgot the WHERE."

By the end of this chapter you'll write single- and multi-row inserts, copy data with INSERT ... SELECT, update rows surgically (and understand exactly why the WHERE is sacred), delete safely, and reach for the modern conveniences — UPSERT and RETURNING — that turn two round-trips into one. Every example runs against the familiar customers and orders tables, and because the playground rebuilds a fresh database on every Run, you can break things freely and run them again.

INSERT: putting rows in

INSERT adds new rows to a table. The safest, most readable form always names the columns you're filling, then supplies matching VALUES:

Notice the shape of that block: the INSERT does the work, but a trailing SELECT is what lets you see it — otherwise the playground would only report "1 row affected." That pattern holds for the whole chapter: every write ends with a read that proves it happened.

Naming columns (and why you always should)

You can omit the column list and rely on positional order — INSERT INTO customers VALUES (6, 'Ngozi', 'Enugu', 33) — but don't. The named form is self-documenting, survives someone adding a column later, and lets you insert columns in any order. The unnamed form silently breaks the day the table's shape changes.

When you name only some columns, the rest take their default — which is NULL unless the column declares otherwise:

Kunle arrives with city and age as NULL — the columns you didn't mention. This is the everyday case: a signup form that captures a name and email now, and fills in the rest later. (If a column is declared NOT NULL with no default, omitting it is an error — the database is protecting your integrity, a topic Part IV covered.)

Many rows at once

Inserting rows one statement at a time is slow — each is a separate trip to the engine. Supply several tuples in one statement instead:

One statement, three rows, one transaction. For bulk loads this is dramatically faster than a loop of single inserts, because the engine commits the work once instead of many times.

INSERT ... SELECT: rows from a query

The VALUES can be replaced entirely by a SELECT. This copies or derives rows from existing data — indispensable for archiving, backfilling, and building summary tables:

Ada and Sam flow straight from customers into the new table — no application code, no round-trip per row. The columns you SELECT must line up, in order, with the columns you're inserting into.

UPDATE: changing rows in place

UPDATE modifies rows that already exist. Its anatomy is three parts: the table, a SET list of column = value assignments, and — critically — a WHERE clause that decides which rows are touched.

Ada has a birthday. Notice age = age + 1 reads the current value and writes the new one in a single statement — the assignment can reference the row's own columns. You can set several columns at once, separated by commas:

Tom moves to Lagos and turns 29 in one write.

The WHERE clause is sacred

Here is the single most important sentence in this chapter: an UPDATE with no WHERE clause changes every row in the table. SQL does exactly what you tell it, and "change the city" with no filter means "change everyone's city." Run this and watch the whole table fall over — safely, because the next Run resets it:

Every customer now lives in Lagos. In the playground that's a shrug and a re-Run. In production, against a live table, that is an incident — pages, apologies, and a scramble to restore from backup. There is no confirmation dialog. The WHERE clause is the only thing standing between "fix one row" and "corrupt them all." We'll return to this in Common Mistakes, because it deserves a horror story.

UPDATE driven by a subquery

Often the rows you want to change are defined by another table. A subquery in the WHERE picks them out. Here we give a loyalty bonus — a 500-unit bump — only to orders placed by Lagos customers:

Orders 1, 2 (Ada, Lagos) and 4 (Sam, Lagos) each gain 500; the rest are untouched. The subquery is evaluated to a set of ids, and only matching orders are updated.

A correlated subquery goes further: it can compute a value for each row from another table. This is how you build denormalized summary columns. Below we create a per-customer spend table, seed it with INSERT ... SELECT, then fill each total from a correlated subquery over orders:

For each row of customer_spend, the inner query re-runs, summing that customer's orders — Lin (the laptop buyer) tops the list. The IFNULL(..., 0) guards customers with no orders so they show 0 instead of NULL. This one statement replaces what would otherwise be a loop of queries in your application.

DELETE: taking rows out

DELETE removes whole rows. Like UPDATE, it lives and dies by its WHERE:

The two 500-unit pen orders are gone; everything at 1000 or above remains. And the same warning applies with the same force — DELETE FROM orders with no WHERE empties the entire table:

Zero rows remaining. Every DELETE and UPDATE you write should make you pause on the WHERE clause the way a driver pauses at a level crossing.

DELETE vs. TRUNCATE

To empty a table, MySQL and PostgreSQL offer TRUNCATE TABLE, which is not a row-by-row delete — it deallocates the table's storage wholesale. It's far faster on large tables, but it's DDL-like: it usually can't be filtered, doesn't fire per-row triggers, often resets auto-increment counters, and may not be transactional the way DELETE is.

-- MySQL / PostgreSQL only — NOT valid in SQLite
TRUNCATE TABLE orders;

SQLite has no TRUNCATE. You use DELETE FROM orders instead — and SQLite quietly optimizes an unfiltered DELETE into a fast "truncate" internally, so you get much of the speed benefit anyway. That's the portable habit: DELETE FROM t empties a table everywhere; reach for TRUNCATE only on engines that have it and when you specifically want its bulk behavior.

UPSERT: insert-or-update in one shot

A constant real-world need: "insert this row, but if a row with the same key already exists, update it instead." Done naively, that's a SELECT to check, then branch to an INSERT or UPDATE — two round-trips and a race condition in between. UPSERT collapses it into a single atomic statement.

SQLite (and PostgreSQL) spell it INSERT ... ON CONFLICT(column) DO UPDATE. The conflict target must be a column with a PRIMARY KEY or UNIQUE constraint — that's what defines "already exists." Watch an inventory restock: PEN exists so its quantity is added to; MUG is new so it's inserted:

The magic word is excluded — a special reference to the row you tried to insert. So qty = qty + excluded.qty means "existing quantity plus the incoming quantity." Result: BOOK untouched at 10, MUG inserted at 15, PEN bumped to 70.

Sometimes you want the opposite — insert if new, but ignore a duplicate rather than update it. That's DO NOTHING:

The second insert hits the UNIQUE constraint on addr, and DO NOTHING swallows the conflict silently — one row, still "Ada," no error thrown.

-- MySQL equivalent of the restock above — NOT valid in SQLite
INSERT INTO inventory (sku, name, qty) VALUES ('PEN', 'Pen', 20)
  ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

RETURNING: get the changed rows back

Normally a write tells you only how many rows it affected. But you often need the rows themselves — most commonly the auto-generated id of a freshly inserted row, so your application can use it immediately. RETURNING (SQLite 3.35+, and long-standing in PostgreSQL) hands them straight back:

The RETURNING clause is a result set, so it displays directly — no follow-up SELECT needed. It works on UPDATE and DELETE too (DELETE FROM orders WHERE amount < 1000 RETURNING * tells you exactly what you removed), which makes it perfect for audit logs and for confirming the effect of a write in the same round-trip. In MySQL, which lacks RETURNING, you'd instead call LAST_INSERT_ID() after the insert — one more reason to know your dialect.

Common Mistakes

• Forgetting the WHERE on UPDATE/DELETE. The flagship disaster. UPDATE accounts SET balance = 0 — with no WHERE — zeroes every account. The fix: write the statement as a SELECT first (SELECT * FROM accounts WHERE ...), confirm it returns exactly the rows you mean, then rewrite SELECT * as UPDATE ... SET / DELETE. Many teams wrap risky writes in a transaction (BEGIN; ... ; then inspect, then COMMIT or ROLLBACK) — the subject of the next chapter.
• Relying on positional INSERT. INSERT INTO t VALUES (...) without a column list breaks silently the day someone adds or reorders a column. The fix: always name your columns.
• Assuming omitted columns get an error. They get their default (usually NULL). A row can arrive half-empty and look fine — until a NOT NULL report chokes on it. The fix: know your defaults; declare NOT NULL where a value is truly required.
• Emulating UPSERT with SELECT-then-INSERT. Between the check and the write, another connection can insert the same key — you get a duplicate or a crash. The fix: use a single atomic ON CONFLICT (or ON DUPLICATE KEY) statement.
• Expecting TRUNCATE in SQLite. It doesn't exist. The fix: DELETE FROM t.
• Building writes by string-concatenating user input. "INSERT ... VALUES ('" + name + "')" is a SQL-injection hole. The fix: always use parameterized queries (? placeholders) from your driver — never glue values into SQL text.

Summary

• DML is three verbs: INSERT adds rows, UPDATE changes them, DELETE removes them. Unlike SELECT, each one changes the truth.
• Always name columns in INSERT; omitted columns take their default (usually NULL). Insert many rows in one statement, and use INSERT ... SELECT to derive rows from a query instead of looping in code.
• The WHERE clause is sacred on UPDATE and DELETE — omit it and you change or destroy every row. Dry-run the filter as a SELECT first.
• Subqueries let a write target or compute values from other tables; correlated subqueries fill per-row summary columns in one statement.
• SQLite has no TRUNCATE — use DELETE FROM t (which SQLite optimizes internally anyway).
• UPSERT (INSERT ... ON CONFLICT(col) DO UPDATE/DO NOTHING) does insert-or-update atomically; the spelling differs sharply across SQLite/Postgres, MySQL, and SQL Server. RETURNING hands the changed rows straight back.

You now know how to change a single row, or a million, in one statement. But real work rarely changes just one thing — a purchase debits one account and credits another, and both must happen or neither may. Next we wrap writes in the guarantee that makes databases trustworthy: the transaction.