Changing Data: INSERT, UPDATE, DELETE

So far everything you've run has been safe - SELECT only reads, so the worst that happens is you get the wrong rows back and try again. This phase is different. INSERT, UPDATE, and DELETE change the table. Used carefully they're completely routine; used carelessly, one of them can ruin an afternoon (or worse). So we'll learn the commands and the habit that keeps you safe - in the same breath.

Same users table as before:

 id │ name           │ email                 │ city        │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
  1 │ Ada Lovelace   │ [email protected]       │ London      │  36 │ 2026-01-04
  2 │ Grace Hopper   │ [email protected]     │ New York    │  41 │ 2026-01-09
  3 │ Alan Turing    │ [email protected]      │ London      │  29 │ 2026-02-15
  4 │ Katherine J.   │ [email protected]       │ Hampton     │  52 │ 2026-03-01
  5 │ Linus T.       │ [email protected]     │ Portland    │  33 │ 2026-03-22

The cheat-card: stay safe while changing data

Before the details, here's the whole survival kit. If you remember nothing else from this phase, remember this:

The thread running through all of it: with UPDATE and DELETE, the WHERE clause is not optional decoration - it's the seatbelt. Now let's go command by command.

INSERT - add a new row

What it actually is. INSERT adds one (or more) brand-new rows to a table - a new user signs up, a new order is placed. You tell it which columns you're filling and what values to put in them. It's the gentlest of the three: it only adds, so it can't overwrite or erase existing rows.

A real example.

INSERT INTO users (name, email, city, age, created_at)
VALUES ('Margaret H.', '[email protected]', 'Boston', 45, '2026-06-19');

INSERT 0 1

What just happened: You added one new user. The first part lists the columns you're filling; VALUES gives the matching values in the same order - name gets 'Margaret H.', email gets the address, and so on. The INSERT 0 1 reply (PostgreSQL's wording) confirms one row was inserted.

Notice we didn't set id. Many tables generate id automatically (an auto-incrementing key), so you leave it out and let the database assign the next number - see Relationships & Keys for how that's set up.

⚠️ Gotcha. The columns list and the VALUES list must line up - same count, same order. If you swap two values, SQL won't catch it as long as the types fit: putting a city where a name goes is a perfectly valid string, so the database happily stores 'Boston' as someone's name. Listing the column names explicitly (rather than relying on table order) makes these mix-ups far less likely.

UPDATE - change rows that already exist

What it actually is. UPDATE modifies values in rows already in the table - someone moves city, fixes a typo, changes their email. You say which column(s) to change, what to change them to, and - critically - which rows. Everything you don't name stays as it was.

A real example.

UPDATE users
SET city = 'Cambridge'
WHERE id = 3;

UPDATE 1

What just happened: You changed exactly one row - Alan Turing, id = 3 - setting his city to 'Cambridge'. His other columns are untouched. The UPDATE 1 reply is a number worth reading: expected one row and it says UPDATE 1, good. Says UPDATE 5? Stop and look.

You can change several columns at once by separating them with commas:

UPDATE users
SET city = 'Cambridge', age = 30
WHERE id = 3;

UPDATE 1

What just happened: Same single row, two columns updated together. The SET list can be as long as you like; WHERE still decides which rows it applies to.

⚠️ The career-defining gotcha: UPDATE with no WHERE

Here's the one that has its own genre of horror stories. Look closely at what's missing:

-- DANGER: no WHERE clause
UPDATE users
SET city = 'Cambridge';

UPDATE 6

What just happened: With no WHERE to narrow it down, the UPDATE applied to every single row. Everyone now lives in Cambridge - Ada, Grace, Alan, Katherine, Linus, and Margaret, all overwritten in one stroke. UPDATE 6 is the database calmly telling you that you just changed six rows. There's no "are you sure?" prompt - SQL did exactly what you told it to.

This is not a rare or beginner-only mistake - experienced people have wiped production tables this way, usually while moving fast. The fix is a habit, not a feature:

💡 Key point - the WHERE-first habit. When writing an UPDATE or DELETE, type the WHERE clause before you type the SET (or before you run anything). Make narrowing the rows the first thing you do, not the last thing you remember. A second habit that pairs with it: run a SELECT with the same WHERE first to see exactly which rows you're about to touch.

-- Look before you leap: see which rows the WHERE matches
SELECT id, name, city
FROM users
WHERE id = 3;

 id │ name        │ city
────┼─────────────┼───────────
  3 │ Alan Turing │ London

What just happened: This is a dry run. You've confirmed WHERE id = 3 matches exactly the one row you mean to change - Alan - before running the UPDATE. Swap the SELECT ... for UPDATE users SET ... keeping the identical WHERE, and you change precisely what you just previewed.

DELETE - remove rows

What it actually is. DELETE removes whole rows from a table - a user closes their account, a record was created by mistake, old data gets cleaned out. Same shape as UPDATE: a WHERE decides which rows go.

A real example.

DELETE FROM users
WHERE id = 5;

DELETE 1

What just happened: The row with id = 5 (Linus T.) is gone - removed from the table. DELETE 1 confirms one row was deleted. As with UPDATE, read that number: your sanity check on how much you just removed.

⚠️ The same trap, sharper: DELETE with no WHERE

DELETE carries the identical gotcha as UPDATE, except the consequence is even more final - you're not overwriting data, you're erasing it:

-- DANGER: no WHERE clause
DELETE FROM users;

DELETE 6

What just happened: With no WHERE, DELETE removed every row in the table. The users table is now empty - all six users gone. The table structure (the columns) still exists, but it holds nothing, and once committed, those rows aren't coming back unless you have a backup. The single most expensive line in this guide, which is exactly why the cheat-card puts "write the WHERE first" in bold.

Your safety net: transactions

Habits prevent most accidents. Transactions catch the rest - here's the idea that makes changing data far less scary.

📝 Terminology. A transaction is a group of changes the database treats as one all-or-nothing unit. You open it with BEGIN, make your changes, then either COMMIT (make them permanent) or ROLLBACK (undo everything since the BEGIN, as if it never happened).

A real example.

BEGIN;

DELETE FROM users
WHERE city = 'London';

-- Check the damage before committing:
SELECT count(*) FROM users;

 count
───────
     4

What just happened: You opened a transaction, ran a DELETE, then peeked at the table. You expected to remove the two London users (Ada and Alan), leaving four, and count(*) says 4. That looks right, so you'd make it permanent:

COMMIT;

But suppose the count had been wrong - say it showed 0, meaning you'd accidentally deleted everyone. Inside a transaction, you're not stuck:

ROLLBACK;

What just happened: ROLLBACK undid every change since BEGIN. The deleted rows snap back as if the DELETE never ran - the seatbelt working: a transaction gives you a chance to look before your change becomes permanent, and a way to back out if it's wrong.

💡 Key point. For any UPDATE or DELETE you're even slightly unsure about, wrap it in a transaction: BEGIN, run it, SELECT to verify, then COMMIT if it's right or ROLLBACK if it's not. It turns "oh no" into "phew." Transactions do more - they're how databases keep data consistent even when many things happen at once, a topic of its own; see Transactions & ACID for the full picture.

⚠️ Gotcha. A transaction only protects you before you commit. Once you run COMMIT, the change is permanent and ROLLBACK can't help. Many tools also run in "autocommit" mode by default - every statement commits instantly unless you explicitly BEGIN first. When the change matters, type BEGIN first.

Recap

1. INSERT INTO ... VALUES ... adds new rows - line your columns up with your values, in order.
2. UPDATE ... SET ... WHERE ... changes existing rows; DELETE FROM ... WHERE ... removes them.
3. The big one: an UPDATE or DELETE with no WHERE hits every row - rewriting or erasing the whole table, with no confirmation prompt.
4. Write the WHERE first, and SELECT with the same WHERE to preview which rows you'll touch. Read the affected-row count the database reports back.
5. Wrap risky changes in a transaction: BEGIN → change → SELECT to check → COMMIT or ROLLBACK. It only protects you until you commit.

You can now read data with SELECT, narrow it with WHERE, sort and limit it, and change it safely with INSERT, UPDATE, and DELETE - the everyday core of SQL, the same handful of shapes you'll use for years. When you're ready to pull data from more than one table at a time, head to SQL Joins, Explained.

← Phase 2: Filtering & Sorting · Guide overview