Updating and deleting

Data changes over time. Users update their profiles. Reviews get edited. Records need corrections. So far we have learned how to insert data and read it back. Now let’s learn how to modify and remove it.

These are the last two pieces of the CRUD puzzle: update and delete. They are also the two statements that cause the most damage when you get them wrong. A bad SELECT just shows you the wrong rows. A bad UPDATE or DELETE changes your data, and once a statement is committed there is no undo. The previous lesson on transactions gave us the right tool for working safely with destructive statements, and you will see it in every example below.

UPDATE

UPDATE modifies existing rows. You specify which table to update, what to change, and which rows to change:

UPDATE users
SET name = 'Alice Doe'
WHERE id = 1;

Let’s break this down.

• UPDATE users targets the users table.
• SET name = 'Alice Doe' says “change the name column to this new value.”
• WHERE id = 1 limits the change to only the row with id equal to 1.

That is the syntax. Now look at it again, this time the way you should actually run it against a database you care about.

The safe way: wrap, verify, commit or rollback

Inside a transaction, an UPDATE is fully visible to your session but not yet permanent. You can run a SELECT to confirm the change touched what you expected, then COMMIT to save it or ROLLBACK to undo. This is the single best habit you can build around UPDATE and DELETE:

BEGIN;
UPDATE users SET name = 'Alice Doe' WHERE id = 1;
SELECT id, name, email FROM users WHERE id = 1;   -- verify the row looks right
COMMIT;                                            -- or ROLLBACK; to back out

Two extra lines, no real cost, and it saves you from the kind of mistake that ruins your afternoon. Every UPDATE and DELETE example for the rest of this lesson will use this pattern. Get used to typing it.

Updating multiple columns

You can update multiple columns at once by separating them with commas:

BEGIN;
UPDATE users SET name = 'Alice Doe', bio = 'Software engineer' WHERE id = 1;
SELECT id, name, bio FROM users WHERE id = 1;
COMMIT;

Now here is something very important. What do you think happens if you forget the WHERE clause?

UPDATE users SET name = 'Alice Doe';

This updates every single row in the table. Every user’s name is now “Alice Doe.” This is exactly the disaster the transaction wrapper exists for: if you ran this inside BEGIN, the SELECT afterward would show every row had changed, you would feel the cold sweat, and you could ROLLBACK to undo. Without the wrapper, there is no undo. Always double-check the WHERE clause, and always have a BEGIN above it.

UPDATE with expressions

The SET clause does not have to be a literal value. You can use expressions, functions, and references to other columns:

BEGIN;
UPDATE users SET name = upper(name) WHERE id = 1;
SELECT id, name FROM users WHERE id = 1;
COMMIT;

upper() is a built-in SQLite function that converts text to uppercase. So if Alice’s name was 'Alice Doe', it becomes 'ALICE DOE'. The SELECT inside the transaction shows you exactly what the function produced before you commit, which is especially valuable for expression-based updates where the new value is not obvious from reading the SQL.

Here is a practical example. Say you want to set a default bio for every user who has not filled one in yet:

BEGIN;
UPDATE users SET bio = 'No bio provided.' WHERE bio IS NULL;
SELECT id, name, bio FROM users WHERE bio = 'No bio provided.';   -- which rows were touched?
COMMIT;

This finds all rows where bio is NULL and sets them to a default value. Only the rows that match the WHERE condition are affected. The SELECT after the UPDATE shows you exactly which rows you just changed; if the count or contents look surprising, ROLLBACK and adjust the query before trying again.

DELETE

DELETE removes rows from a table:

BEGIN;
DELETE FROM users WHERE id = 3;
SELECT * FROM users;   -- look around: did the right row disappear?
COMMIT;                 -- or ROLLBACK; to bring it back

The row with id 3 is gone permanently after COMMIT. Until then, you can ROLLBACK to put it back. Notice the same pattern: wrap, verify with a SELECT, then commit or roll back depending on what you saw.

Just like UPDATE, a DELETE without WHERE affects every row:

DELETE FROM users;

This empties the entire table. The table itself still exists (with its columns and constraints), but all data is gone. Inside a transaction, the SELECT afterward would return zero rows and your stomach would drop, but a ROLLBACK would put everything back. Outside a transaction, every row is gone and there is nothing you can do. If you want to remove the table entirely, including its structure, use DROP TABLE instead, and do it in a migration.

The wrap-then-verify pattern is the difference between “I almost wiped my whole users table” and “I wiped my whole users table.” Make it a reflex.

Returning modified rows

When you update or delete a row, you often need to see what changed. So far we have been using a follow-up SELECT inside the transaction for that. SQLite 3.35+ provides a shortcut with the RETURNING clause that gives you the affected rows directly from the UPDATE or DELETE itself:

BEGIN;
UPDATE users SET name = 'Bob Smith' WHERE id = 2
RETURNING id, name, email;
COMMIT;   -- or ROLLBACK; if the returned row is not what you expected

id  name       email
--  ---------  ---------------
2   Bob Smith  [email protected]

RETURNING attaches to the end of an INSERT, UPDATE, or DELETE statement and returns the affected rows as part of the same statement. It saves you from writing a separate SELECT and guarantees you see the exact row that was just modified. Inside a transaction, that combination is gold: you change the row, you see exactly what changed, and you decide whether to commit or roll back.

It works with DELETE too:

BEGIN;
DELETE FROM users WHERE id = 4 RETURNING *;
COMMIT;   -- or ROLLBACK; if you deleted the wrong row

The deleted row comes back so you can confirm what was removed before committing. If it is not what you meant to delete, ROLLBACK and the row is still there.

And with INSERT:

INSERT INTO users (email, name) VALUES ('[email protected]', 'Frank')
RETURNING id;

This inserts a row and immediately returns the auto-generated id. A single INSERT is already atomic, so there is no safety reason to wrap this one in a transaction. The RETURNING is just a cleaner alternative to calling last_insert_rowid() separately.

[!WARNING] Two reflexes worth burning in: always include a WHERE clause with UPDATE and DELETE unless you genuinely want to affect every row, and always wrap destructive statements in a transaction so you can preview the result and ROLLBACK if needed. They cost nothing to add and they save you from the most common (and worst) mistakes in SQL.

Exercises

Exercise 1: Wrap an UPDATE in BEGIN ... COMMIT, run a SELECT between them to inspect the change, and confirm the row looks right before committing. Then try the same flow but use ROLLBACK instead of COMMIT and verify the change was undone.

Exercise 2: Use UPDATE (inside a transaction) with an expression: set every user’s bio to 'Hello, I am ' || name (the || operator concatenates strings in SQLite). Run a SELECT inside the transaction to verify the result before committing.

Exercise 3: Inside a transaction, delete a user using RETURNING * so you can see the deleted row. Decide whether the result is what you expected, then COMMIT or ROLLBACK accordingly.

We have now covered all four CRUD operations: create (INSERT), read (SELECT), update (UPDATE), and delete (DELETE). Every destructive example in this lesson, and every multi-step example since the transactions lesson earlier in this section, has been wrapped in BEGIN ... COMMIT so you can preview and roll back. That is the habit. So far, all our data lives in a single users table, created by a single migration. That is enough to learn the basics, but real applications have users, authors, books, tags, reviews, and all kinds of other things that connect to each other.

In the next section, we step back and think about schema design. How do you split data across multiple tables? How do tables reference each other? What does good data modeling look like? We will get into relationships, normalization, and the practices that keep your database healthy as it grows.