Data Changes

Delete Rows

Removing Matches

DELETE removes rows that match a condition.

Program

Play the script to remove completed queue items.

delete_rows.sql
Replay: real traced execution (multi-file project)
CREATE TABLE queue (id INTEGER, task TEXT, done INTEGER);
INSERT INTO queue VALUES (1, 'ship', 1), (2, 'reply', 0), (3, 'archive', 1);
DELETE FROM queue WHERE done = 1;
SELECT id, task FROM queue ORDER BY id;

  1. queue ← 0 rows

    1CREATE TABLE queue (id INTEGER, task TEXT, done INTEGER);2INSERT INTO queue VALUES (1, 'ship', 1), (2, 'reply', 0), (3, 'archive', 1);
    values this step0 rowsqueue

  2. queue ← 3 rows

    1CREATE TABLE queue (id INTEGER, task TEXT, done INTEGER);2INSERT INTO queue VALUES (1, 'ship', 1), (2, 'reply', 0), (3, 'archive', 1);3DELETE FROM queue WHERE done = 1;
    values this step3 rowsqueue

  3. queue ← 1 row

    2INSERT INTO queue VALUES (1, 'ship', 1), (2, 'reply', 0), (3, 'archive', 1);3DELETE FROM queue WHERE done = 1;4SELECT id, task FROM queue ORDER BY id;
    values this step1 rowqueue

  4. result ← 1 row

    3DELETE FROM queue WHERE done = 1;4SELECT id, task FROM queue ORDER BY id;
    values this step1 rowresult

Follow the Delete

  1. The queue starts with (1, ship, 1), (2, reply, 0), and (3, archive, 1).
  2. DELETE WHERE done = 1 matches ship and archive.
  3. reply has done = 0, so it stays.
  4. The final SELECT id, task returns only (2, reply). | before row | done | result | | --- | --- | --- | | (1, ship) | 1 | deleted | | (2, reply) | 0 | kept | | (3, archive) | 1 | deleted |
DELETE `DELETE FROM queue` removes rows.
condition `WHERE done = 1` protects rows that are not done.
remaining rows The final table contains only rows that survived the delete.

Exercise: delete_rows.sql

Reproduce the remaining row reply, then identify which starting rows matched done=1 before the delete.