Single-row insert

The most basic write. List the target columns (optional but recommended) and the values in matching order.

-- Explicit column list — safe against future schema changes
INSERT INTO users (id, email, full_name, created_at)
VALUES (4, 'margaret@example.com', 'Margaret Hamilton', '2025-03-01 08:00:00');

-- Without column list — works, but brittle if someone adds a column later
INSERT INTO users
VALUES (5, 'alan@example.com', 'Alan Turing', '2025-03-02 09:00:00');

Multi-row insert

Multiple VALUES tuples in a single statement — one round trip to the database, one parse, one WAL flush. Dramatically faster than issuing 1,000 individual inserts from application code.

INSERT INTO products (id, sku, name, price, category)
VALUES
  (104, 'BK-003', 'Clean Code',      32.00, 'books'),
  (105, 'MG-002', 'SQL Sticker',      2.50, 'merch'),
  (106, 'BK-004', 'The Art of SQL',  29.99, 'books');

INSERT … SELECT — copy from another query

Instead of literal values, feed the INSERT from any SELECT. Powerful for ETL steps, archiving, or populating summary tables. The column count and types must match.

-- Archive cancelled orders into a separate table (hypothetical)
INSERT INTO archived_orders (id, user_id, status, placed_at)
SELECT id, user_id, status, placed_at
FROM   orders
WHERE  status = 'cancelled';

Handling conflicts — upsert preview

What if the row already exists? By default a duplicate primary key (or unique constraint) causes an error. ON CONFLICT (PostgreSQL / SQLite) or ON DUPLICATE KEY UPDATE (MySQL) lets you decide: skip it, or update the existing row. The full pattern is covered in Chapter 11 — here's just enough to know it exists.

-- PostgreSQL / SQLite: insert or skip if email already exists
INSERT INTO users (id, email, full_name)
VALUES (4, 'margaret@example.com', 'Margaret Hamilton')
ON CONFLICT (email) DO NOTHING;

-- PostgreSQL: insert or update on conflict
INSERT INTO users (id, email, full_name)
VALUES (4, 'margaret@example.com', 'Margaret H.')
ON CONFLICT (email) DO UPDATE
  SET full_name = EXCLUDED.full_name;

-- MySQL equivalent
INSERT INTO users (id, email, full_name)
VALUES (4, 'margaret@example.com', 'Margaret H.')
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);

UPDATE changes values in existing rows. The WHERE clause decides which rows are affected. Forgetting it affects every row in the table — the single most destructive mistake in all of SQL.

Basic UPDATE with WHERE

-- Set one order's status to 'shipped'
UPDATE orders
SET    status = 'shipped'
WHERE  id = 1001;

-- Update multiple columns at once
UPDATE users
SET    email      = 'ada.lovelace@example.com',
       full_name  = 'Augusta Ada Lovelace'
WHERE  id = 1;

The accidental full-table update

Run this without the WHERE and every order becomes 'shipped':

-- ⚠ DANGER: missing WHERE → ALL rows affected
UPDATE orders SET status = 'shipped';
-- 4 rows updated (every single order, including cancelled ones)

Safe practice — preview before writing

Before running any UPDATE, run the same WHERE as a SELECT and verify the row set. Then wrap the UPDATE in a transaction (§4) so you can ROLLBACK if the results aren't what you expect.

-- Step 1: preview
SELECT id, status FROM orders WHERE id = 1001;

-- Step 2: update inside a transaction
BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 1001;
-- Inspect the result…
SELECT id, status FROM orders WHERE id = 1001;
-- Happy? Commit. Not happy? ROLLBACK;
COMMIT;

UPDATE … FROM — updating from another table

Sometimes the new value comes from a related table. PostgreSQL and SQL Server support UPDATE … FROM; MySQL uses UPDATE … JOIN; standard SQL uses a correlated subquery. All three produce the same result.

-- PostgreSQL: set each order's status based on a lookup table
UPDATE orders o
SET    status = s.new_status
FROM   status_transitions s
WHERE  s.order_id = o.id
  AND  s.approved = true;

-- MySQL equivalent
UPDATE orders o
JOIN   status_transitions s ON s.order_id = o.id AND s.approved = true
SET    o.status = s.new_status;

-- Standard SQL (correlated subquery)
UPDATE orders
SET    status = (
  SELECT s.new_status
  FROM   status_transitions s
  WHERE  s.order_id = orders.id AND s.approved = true
)
WHERE  id IN (SELECT order_id FROM status_transitions WHERE approved = true);

DELETE removes rows that match a WHERE. TRUNCATE removes all rows, fast. They differ in speed, logging, and how they interact with foreign keys and transactions.

DELETE with a WHERE clause

Just like UPDATE, a missing WHERE deletes every row. The same "preview with SELECT, wrap in a transaction" discipline applies.

-- Delete a specific cancelled order
DELETE FROM orders WHERE id = 1004;

-- Delete all cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

-- ⚠ Delete EVERYTHING — rarely what you want
DELETE FROM orders;  -- 0 rows remain

Cascade deletes and referential integrity

If order_items has a foreign key pointing at orders.id, deleting an order that still has items will fail with a constraint violation — unless the FK was defined with ON DELETE CASCADE (Chapter 07). Three possible outcomes:

-- If FK is ON DELETE RESTRICT (the default):
DELETE FROM orders WHERE id = 1001;
-- ERROR: update or delete on table "orders" violates foreign key constraint on "order_items"

-- If FK is ON DELETE CASCADE:
DELETE FROM orders WHERE id = 1001;
-- order 1001 deleted, AND all its order_items automatically deleted

-- If FK is ON DELETE SET NULL:
DELETE FROM orders WHERE id = 1001;
-- order_items.order_id set to NULL for those rows (usually undesirable)

TRUNCATE vs DELETE

Both empty a table, but the mechanism is fundamentally different.

-- DELETE (slow, safe, triggers fire, rollback works everywhere)
DELETE FROM order_items;
DELETE FROM orders;

-- TRUNCATE (fast, resets sequences, no triggers)
TRUNCATE order_items, orders;

-- PostgreSQL: cascade to children + reset IDs
TRUNCATE orders CASCADE;
-- Also truncates order_items (FK child) and resets sequences.

A transaction wraps one or more statements into a unit that either succeeds completely (COMMIT) or fails completely (ROLLBACK). No in-between. If the server crashes mid-transaction, the database acts as though nothing happened.

BEGIN, COMMIT, ROLLBACK

-- Transfer $50 from user 1 to user 2's account balance (hypothetical)
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE user_id = 1;
  UPDATE accounts SET balance = balance + 50 WHERE user_id = 2;
COMMIT;
-- Both succeed, or neither does. No partial state is ever visible to other sessions.

-- If something goes wrong mid-way:
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE user_id = 1;
  -- Oops, user 2's account is locked / doesn't exist / whatever
ROLLBACK;
-- The first UPDATE is undone — user 1's balance is unchanged.

ACID — the four guarantees

Every serious database promises these four properties. They're what separates a database from a folder full of CSV files.

When and why to wrap statements in a transaction

Three common patterns where an explicit transaction is essential:

-- ① Multi-statement write that must be atomic
BEGIN;
  INSERT INTO orders (id, user_id, status, placed_at)
  VALUES (1005, 1, 'pending', NOW());

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (1005, 101, 2, 34.99);
COMMIT;
-- If the second INSERT fails (e.g. product 101 doesn't exist), both are rolled back.

-- ② Read-then-write where the read must not go stale
BEGIN;
  SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;
  -- "FOR UPDATE" locks the row so no other transaction can change it mid-flight.
  UPDATE accounts SET balance = balance - 50 WHERE user_id = 1;
COMMIT;

-- ③ Exploratory writes in production (the "safety net" pattern)
BEGIN;
  DELETE FROM order_items WHERE order_id = 1001;
  DELETE FROM orders WHERE id = 1001;
  -- Check: did we delete exactly 1 order and 2 items?
  -- SELECT COUNT(*) FROM orders WHERE id = 1001; → should be 0
  -- If wrong, type: ROLLBACK;
COMMIT;