INSERT, UPDATE, and DELETE — changing data

SELECT reads data without changing anything. These three statements are the entire write side of SQL — and two of them are one missing WHERE clause away from a very bad day.

Beginner

4 min read

INSERT: adding a new row

INSERT INTO users (name, email, age) VALUES ('Ada', 'ada@example.com', 36);

INSERT INTO table (columns) VALUES (values) adds exactly one new row — the columns and values lists have to line up positionally, in the same order. The id column isn't listed here at all: if it's set up as an auto-incrementing primary key (from the previous lesson), the database assigns the next value automatically, and explicitly providing one is usually unnecessary and can even conflict with a value the database was about to assign itself.

INSERT INTO users (name, email, age) VALUES
    ('Grace', 'grace@example.com', 41),
    ('Alan', 'alan@example.com', 29);

Multiple rows can be inserted in a single statement by listing several value groups — this is both more concise and meaningfully faster than issuing one INSERT per row, since it's one round trip to the database instead of many.

UPDATE: changing existing rows — and the clause that makes it safe

UPDATE users SET age = 37 WHERE id = 1;

UPDATE table SET column = value WHERE condition changes the matching rows' specified columns. The WHERE clause here isn't optional in any practical sense — it's what limits the update to specific rows:

UPDATE users SET age = 37;   -- no WHERE — sets EVERY row's age to 37

Without WHERE, UPDATE applies to every single row in the table, silently and immediately — no confirmation prompt, no dry run by default. This is one of the most common, genuinely damaging mistakes in SQL: forgetting the WHERE clause, or getting its condition wrong, turns "fix one person's age" into "overwrite everyone's age" in a single keystroke-away mistake. Multiple columns can be set in one statement: UPDATE users SET age = 37, email = 'new@example.com' WHERE id = 1.

DELETE: removing rows — the same danger, more permanent

DELETE FROM users WHERE id = 1;

DELETE FROM table WHERE condition removes matching rows entirely. The exact same danger as UPDATE applies, with a less reversible consequence:

DELETE FROM users;   -- no WHERE — deletes EVERY row in the table

DELETE FROM users with no WHERE empties the entire table. Unlike a mistaken UPDATE (where the old values might still be reconstructible from application logs or a backup), a mistaken bulk DELETE is data that's simply gone, immediately, unless a database backup or transaction rollback happens to save it. This single-WHERE-clause danger is exactly why real production systems treat direct UPDATE/DELETE access with real caution — restricted permissions, required code review on migrations, or a SELECT with the same WHERE clause run first to confirm exactly which rows would be affected, before running the actual UPDATE/DELETE.

The habit that prevents the expensive mistake

-- Step 1: confirm exactly which rows would be affected
SELECT * FROM users WHERE age > 60 AND active = false;
 
-- Step 2: once the result looks right, run the real statement with the same WHERE
DELETE FROM users WHERE age > 60 AND active = false;

Running the equivalent SELECT with the exact same WHERE clause first — checking that the returned rows are genuinely the ones meant to be changed or removed — before running the real UPDATE/DELETE is standard practice specifically because of how unforgiving these statements are. It costs one extra query and catches a mistake before it's made irreversible, rather than after.

Where transactions fit in — a preview

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK, if something looked wrong before committing

INSERT, UPDATE, and DELETE can be wrapped in a transaction (BEGIN ... COMMIT), which lets a mistake be undone with ROLLBACK before it's made permanent — this is the practical, hands-on version of the atomicity guarantee the system design and Django lessons reference when discussing ACID transactions. A future lesson in this domain covers transactions in depth; the short version for now is that wrapping risky writes in a transaction is another real layer of protection against exactly the "forgot the WHERE clause" mistake this lesson focuses on.