Changing and removing rows

How do you change values in existing rows?

UPDATE changes values in rows that are already there. Name the table, say which columns get which new values with SET, and say which rows with WHERE: UPDATE parts SET stock = 12 WHERE id = 6; sets the chain's stock to 12 and touches nothing else.

The new value can be a calculation that uses the old one. SET price_pence = price_pence + 200 reads each row's current price, adds 200 and writes it back. SET stock = stock - 1 records one part used. To change several columns at once, separate them with commas: SET price_pence = 2600, stock = 10.

After running an update, open the Changes view. Changed cells are yellow, with the old value next to the new one, so you can check every row the statement touched.

UPDATE parts
SET price_pence = price_pence + 200,
  stock = stock + 5
WHERE id = 6;

SELECT id, name, price_pence, stock
FROM parts
WHERE id = 6;

How does WHERE decide which rows change?

The WHERE in an UPDATE or DELETE works exactly like the one in a SELECT. Every row for which the condition is true is changed or removed; every other row is left alone. All the tools from filtering apply: =, IN, BETWEEN, LIKE, AND, OR, IS NULL.

To change one specific row, filter on its primary key: WHERE id = 6. A name or a bike model can match more than one row (Owen Price has two repairs on his Raleigh), while an id can only ever match one.

Watch AND and OR closely, because the cost of getting them wrong is higher here. WHERE category = 'retired' OR stock = 0 matches retired parts with stock left as well as any out-of-stock part in any category. If you mean both conditions together, write AND.

What happens if you forget the WHERE?

Every row in the table is changed or deleted. UPDATE repairs SET status = 'done'; marks all eight repairs as done. DELETE FROM repairs; empties the table. SQLite does not ask "are you sure?" and, outside a transaction, there is no undo. The only way back is a backup.

This is one of the most common serious mistakes in real systems, and it usually happens when someone writes the UPDATE line, runs it by accident before adding the WHERE, or highlights only part of the statement in a tool. It also happens with a WHERE that is present but wrong, such as a typo that makes it always true.

Here every run starts from a fresh copy, so experiment freely: run an update with no WHERE and look at the Changes view. Seeing every row turn yellow at once is a good lesson to have learned somewhere harmless.

-- Every row changes. Try it here, never on real data.
UPDATE repairs
SET status = 'done';

SELECT id, customer, status FROM repairs;

How do you preview a change before running it?

Write the SELECT first. Use the same table and the same WHERE you plan to use, and look at the rows it returns. Count them. Are they the rows you meant? Is anything there that should not be?

When the preview is right, turn it into the change. For a delete, replace SELECT * with DELETE. For an update, replace SELECT ... FROM with UPDATE and add the SET line. The WHERE stays exactly as you tested it, character for character.

With an UPDATE, you can preview the new values too: select the old value next to the calculation, as below. If the new prices look wrong in the preview, they would have been wrong in the table.

SELECT id, name, price_pence,
  price_pence + 200 AS new_price
FROM parts
WHERE category = 'brakes';

How do you remove rows?

DELETE FROM table WHERE condition; removes every row that matches. There is no column list, because a delete takes whole rows. To clear one value instead of a whole row, use UPDATE ... SET column = NULL.

Deleting a row whose id other tables point at is blocked when foreign keys are enforced: deleting a customer who still has repairs fails with FOREIGN KEY constraint failed. That is the database protecting you from orphaned rows. Delete or move the child rows first, or decide on a different rule when designing the tables.

Many real systems avoid deleting at all. They keep the row and mark it, as this shop does with status = 'cancelled' or category = 'retired', so the history stays available for reports. Delete when the data really has no further use, or when the law says you must remove it.

DELETE FROM repairs
WHERE status = 'cancelled';

SELECT id, customer, status FROM repairs;

What does RETURNING show you?

Add RETURNING and a column list to the end of an UPDATE, DELETE or INSERT, and the statement hands back the affected rows as a result, with the values after the change. It is like running the change and the check in one step.

That makes mistakes visible at once. If you expected one row back and got eight, you know immediately. It is also how an application learns the id of a row it just inserted, or the new stock level after taking a part off the shelf.

SQLite has supported RETURNING since version 3.35, released in 2021. PostgreSQL has it too. MySQL does not; MariaDB supports it for INSERT and DELETE.

UPDATE parts
SET stock = stock - 1
WHERE id = 5
RETURNING id, name, stock;

How should you check AI-written UPDATE and DELETE statements?

AI tools write changes as confidently as they write queries, and a wrong change cannot be fixed by running it again. Before you run any generated UPDATE or DELETE, go through this list.

• Find the WHERE. If there is none, the statement touches every row. Make sure that is what you want.
• Turn the statement into a SELECT with the same WHERE and count the rows. Compare that number with what you expected.
• Check AND and OR, and any brackets around them. Mixed conditions without brackets are a common source of over-wide matches.
• Check that text values match the real data exactly: 'Cancelled' and 'cancelled' are different to = in SQLite.
• Add RETURNING * or open the Changes view after running, and read the affected rows.
• On real data, work inside a transaction or after a backup. The lesson on transactions shows how to try a change and roll it back.

Field notes

• UPDATE uses SET for the new values and WHERE to pick the rows to change.
• Forgetting the WHERE changes or deletes every row in the table.
• Run a SELECT with the same WHERE first, and check the rows it returns.
• DELETE removes whole rows, so it has no column list.

Easy to get wrong

Without WHERE, every repair is marked done. Aim at one row with its primary key.

OR removes rows that pass either test. The job needed rows that pass both.

The first sets every brake part's price to 200. To raise a value, add to the old value.

DELETE removes whole rows and takes no column list. Clearing one value is an UPDATE.

Questions people ask

How do I know how many rows changed?

The Result view reports it after each statement. In code, SQLite's changes() function returns the count, and RETURNING lists the rows themselves.

Can UPDATE use values from another table?

Yes, with a subquery in SET or WHERE, or with UPDATE ... FROM in SQLite 3.33 and later. Subqueries come in a later lesson.

What is the difference between DELETE and DROP TABLE?

DELETE removes rows and keeps the table. DROP TABLE removes the table itself, structure and all.

Is there a way to empty a table quickly?

DELETE FROM table; with no WHERE. SQLite optimises it. Other databases have TRUNCATE for the same job.