SQL chapters

Chapter 15 of 17

Insert, update and delete safely

Try this in our SQLite Compiler →

The idea

SQL reads and writes database records rather than process stdin. INSERT adds records, UPDATE changes matching records, and DELETE removes matching records. A standalone practice script supplies its own sample data. Applications must validate inputs and bind parameters separately.

Worked example

DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER);
INSERT INTO tasks VALUES (1, 'Read SQL', 0), (2, 'Practice', 0);
UPDATE tasks SET done = 1 WHERE id = 1;
DELETE FROM tasks WHERE id = 2;
SELECT * FROM tasks;

What to check

Only record 1 remains, with done equal to 1. Both write operations targeted an explicit id.

Practice task

Add a third task and update only that task. SELECT before and after each write to verify the intended records.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on