UPDATE & DELETE

Prev Next

Data changes after it's inserted — a recipe gets a better prep-time estimate, a user deletes their account. UPDATE changes existing rows in place, and DELETE removes rows entirely. Both are built around the exact same WHERE clause you already know from SELECT — and both are genuinely dangerous if you forget it.

UPDATE — Changing Existing Rows

UPDATE names a table, a SET clause listing which columns get new values, and (almost always) a WHERE clause saying which rows to touch.

UPDATE recipes
SET prep_minutes = 15
WHERE title = 'Creamy Garlic Pasta';

-- Update more than one column at once
UPDATE recipes
SET difficulty = 'medium', prep_minutes = 25
WHERE id = 1;

A column can also be set relative to its own current value, which is useful for counters:

-- Bump every recipe's prep time by 5 minutes to account for prep-list overhead
UPDATE recipes
SET prep_minutes = prep_minutes + 5
WHERE difficulty = 'hard';

DELETE — Removing Rows

DELETE FROM removes whole rows that match a WHERE clause. There's no column list — a row either matches and gets deleted entirely, or it doesn't match and is left untouched.

DELETE FROM reviews
WHERE id = 42;

-- Remove every review that was left as spam-testing, rating of 0
DELETE FROM reviews
WHERE rating = 0;

The WHERE Clause Is Not Optional (Even Though SQLite Lets You Skip It)

SQLite will happily run an UPDATE or DELETE with no WHERE clause at all — and when it does, the statement applies to every single row in the table, not just the "current" one or the last one you looked at. There is no implicit "just this row." This is one of the most common ways real production data gets destroyed by accident.

-- DANGER: sets difficulty to 'easy' for every recipe in the entire table
UPDATE recipes SET difficulty = 'easy';

-- DANGER: deletes every single review in the entire table
DELETE FROM reviews;

Note: Before running an UPDATE or DELETE against real data, run the same condition as a SELECT first — SELECT * FROM reviews WHERE rating = 0; — and check that it returns exactly the rows you intend to change. Only swap it to UPDATE/DELETE once you've confirmed that.

Putting It Together

A safe, deliberate cleanup pass on RecipeBox: first look, then act.

-- Step 1: check what would be affected
SELECT id, title, prep_minutes FROM recipes WHERE prep_minutes IS NULL;

-- Step 2: fix those specific rows
UPDATE recipes
SET prep_minutes = 30
WHERE prep_minutes IS NULL;

-- Step 3: remove one specific stale review by id, never by a broad guess
DELETE FROM reviews WHERE id = 17;

Next: What happens when an insert would normally fail because the row already exists — but you actually want to update it instead? That's UPSERT (ON CONFLICT), next up.

Prev Next