Insert, update, and delete rows

SQLite uses the same basic data-changing statements you learned in the SQL course:

INSERT INTO notes (title) VALUES ('Plan the week');
UPDATE notes SET title = 'Plan Monday' WHERE id = 1;
DELETE FROM notes WHERE id = 1;

Naming the columns in the INSERT matters. INSERT INTO notes VALUES (...) relies on column order, and it breaks the first time a migration adds a column. Columns you leave out get their default — here id is assigned automatically because it’s the INTEGER PRIMARY KEY.

You can insert several rows in one statement, which is also faster because SQLite commits once instead of once per row:

INSERT INTO notes (title) VALUES
  ('Plan the week'),
  ('Review pull requests'),
  ('Write the changelog');

Get data back from a write

SQLite supports RETURNING (since 3.35), which hands you values from the rows a statement touched:

INSERT INTO notes (title) VALUES ('Call the accountant')
RETURNING id;
-- 4

That saves the separate SELECT last_insert_rowid() round trip, and it works on UPDATE and DELETE too.

Verify what a statement did

The shell doesn’t print anything for a successful write, so ask how many rows the last statement changed:

UPDATE notes SET title = 'Plan Monday' WHERE id = 1;
SELECT changes();
-- 1

changes() returning 0 after an update you expected to work usually means the WHERE clause matched nothing — a wrong id, a typo in a title. The statement didn’t fail; it just found no target. Check with a SELECT using the same WHERE.

The missing WHERE clause

The classic disaster in every SQL database:

UPDATE notes SET title = 'Plan Monday';

No WHERE, so every row in the table now has the same title. DELETE FROM notes; with no WHERE empties the table the same way. SQLite executes both without hesitation and there is no undo outside a transaction or a backup.

Run the matching SELECT first when a WHERE clause affects important data. SELECT count(*) FROM notes WHERE id = 1; costs a second and tells you exactly how many rows the write will touch. For anything beyond a throwaway database, wrap risky changes in BEGIN … COMMIT so a wrong result can still be rolled back — transactions get their own lesson shortly.