Lesson 14 of 20
In progress
INSERT, UPDATE and DELETE
Add rows, change them safely and remove them on purpose, with the checks that stop a missing WHERE from emptying a table.
Reading queries are safe to run twice. Write statements are not, and that asymmetry is why they deserve more care than a SELECT.
What you will learn
- Insert single rows and many rows at once.
- Update with a checked predicate.
- Delete without losing data you meant to keep.
INSERT with named columns
Editable. Runs in your browser.
INSERT INTO tasks (title, owner) VALUES ('Write the draft', 'Ana');
INSERT INTO tasks (title, owner) VALUES ('Review the draft', 'Ravi');
SELECT * FROM tasks;
Press Run to see the output.
Naming the columns is not optional in practice. INSERT INTO tasks VALUES (...) depends on the column order, so adding a column to the table breaks every such statement. The named form keeps working and lets defaults apply to the columns you omit.
id is omitted and filled by the database because it is the primary key, and the defaults supply done and created_on.
Inserting several rows
Editable. Runs in your browser.
Press Run to see the output.
One statement, three rows. It is faster than three statements and atomic in most databases: either all rows are inserted or none. That matters when an import fails halfway.
For copying rows from another table, use INSERT INTO ... SELECT:
Editable. Runs in your browser.
INSERT INTO archive (id, title, owner)
SELECT id, title, owner FROM tasks WHERE done = 1;
Press Run to see the output.
UPDATE
Editable. Runs in your browser.
Press Run to see the output.
SET names the columns and their new values; WHERE chooses the rows. Without WHERE, every row is updated — the statement succeeds, and the damage is silent.
The safe routine:
- Run the predicate as a
SELECTand check the row count and the sample. - Run the
UPDATEwith the same predicate. - Verify with a
SELECTafterwards, or check the reported row count.
Editable. Runs in your browser.
UPDATE tasks
SET done = 0
WHERE owner = 'Ana' AND done = 1;
Press Run to see the output.
Several columns can be set in one statement, and the right-hand side can reference existing values:
Editable. Runs in your browser.
Press Run to see the output.
In standard SQL, the assignments in a single UPDATE all read the old values. Do not assume left-to-right evaluation.
UPDATE with a join
SQLite supports UPDATE ... FROM, which lets a value come from another table:
Editable. Runs in your browser.
UPDATE tasks
SET done = 1
WHERE owner IN (SELECT name FROM owners WHERE active = 0);
Press Run to see the output.
The subquery form works everywhere and reads clearly. Always check the subquery on its own first, because a wrong subquery updates the wrong rows just as quietly.
DELETE
Editable. Runs in your browser.
Press Run to see the output.
The same rule applies: no WHERE, no rows left. A DELETE inside a transaction is recoverable with ROLLBACK; outside one, it is not.
Editable. Runs in your browser.
Press Run to see the output.
Running the delete in a transaction, checking the count, then committing or rolling back is the habit that turns a mistake into a shrug.
For large tables, deleting everything row by row is slow. TRUNCATE in some databases removes all rows quickly; SQLite offers DELETE FROM table without a WHERE or DROP TABLE plus recreation. Either way, confirm there is a backup.
Reading the affected count
Most clients report how many rows a statement changed. Compare that number with the count your SELECT predicted before running the statement, and treat a mismatch as a stop sign: it means the predicate matched a different population than you tested against, and the next statement will do the same.
Return values
Most databases can return the inserted or changed rows. SQLite supports RETURNING:
Editable. Runs in your browser.
Press Run to see the output.
That saves a second query to find the generated id, which is otherwise the usual source of a race in concurrent code.
Transactions around a change
A write that touches several tables belongs in one transaction:
Editable. Runs in your browser.
Press Run to see the output.
If the second statement fails, ROLLBACK leaves the first undone. Without the transaction, the database keeps the successful half and the data no longer tells a coherent story.
Running DELETE FROM tasks while developing with the intention of "just clearing the test rows". Confirm the connection and the database name before a statement without WHERE; that is the one that ends up on production.
Archive every completed task into the archive table, then delete the archived rows from tasks, all inside a transaction. Verify the counts before committing.
Show solution
Editable. Runs in your browser.
INSERT INTO archive (id, title, owner)
SELECT id, title, owner FROM tasks WHERE done = 1;
SELECT COUNT(*) AS to_delete FROM tasks WHERE done = 1;
SELECT COUNT(*) AS archived FROM archive;
DELETE FROM tasks WHERE done = 1;
SELECT COUNT(*) AS remaining FROM tasks;
COMMIT;
Press Run to see the output.
The counts before the delete should match the archived rows, and the remaining count should be the previous total minus the deleted rows. ROLLBACK instead of COMMIT reverses everything if a number is wrong.
Recap
- Name the columns in
INSERT; insert many rows in one statement for atomicity. - Run the
WHEREpredicate as aSELECTbefore anyUPDATEorDELETE. - Wrap multi-statement changes in a transaction and verify before committing.
Next, you will create the tables these statements write to, with types and defaults chosen deliberately.
What does INSERT OR IGNORE do?
Can I undo a DELETE?
Progress is saved in this browser only. No account, no email.