SQL INSERT, UPDATE, and DELETE

Before you start

SQL SELECT with WHERE.

Add, change, and remove rows, then check the result.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

INSERT adds rows, UPDATE changes columns of existing rows, and DELETE removes rows. UPDATE and DELETE act on every row they match, so a WHERE clause is what limits them to the rows you mean.

In the example, done defaults to 0 for new rows. Only the task titled write is marked done, then the task titled ship is removed, and the final SELECT confirms the table holds two rows.

Code example

SQL

CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0);
INSERT INTO tasks (title) VALUES ('write'), ('test'), ('ship');
UPDATE tasks SET done = 1 WHERE title = 'write';
DELETE FROM tasks WHERE title = 'ship';

SELECT id, title, done FROM tasks ORDER BY id;

Output

1|write|1
2|test|0

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0);

2INSERT INTO tasks (title) VALUES ('write'), ('test'), ('ship');

3UPDATE tasks SET done = 1 WHERE title = 'write';

4DELETE FROM tasks WHERE title = 'ship';

6SELECT id, title, done FROM tasks ORDER BY id;

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Three rows get ids 1 to 3 and done = 0 from the default.	`1 write 0, 2 test 0, 3 ship 0`	—
Only the row titled write changes.	`1 write 1, 2 test 0, 3 ship 0`	—
The row titled ship is removed.	`1 write 1, 2 test 0`	—
Read back the two remaining rows.	`1 write 1, 2 test 0`	1\|write\|1 2\|test\|0

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

Cost and common mistakes

Each statement scans or uses an index to find matching rows, O(n) without an index on the filtered column.

UPDATE or DELETE without WHERE changes or removes every row.
Run a SELECT with the same WHERE first to preview which rows will be affected.

Try it yourself

Mark every task as not done again.

Reveal answer and reasoning

UPDATE tasks SET done = 0; with no WHERE, the change applies to all rows.