Changing data: DML and TCL
SQL statements fall into families. So far you have written DML - the part that reads and changes data. This lesson finishes DML's write side and adds TCL, the family that controls when those changes become permanent.
- DML (Data Manipulation Language) works with the rows:
SELECT,INSERT,UPDATE,DELETE,TRUNCATE. - TCL (Transaction Control Language) works with the transaction around those changes:
COMMIT,ROLLBACK,SAVEPOINT.
(Two more families exist and come later: DDL defines structure - CREATE, ALTER, DROP - and DCL manages permissions - GRANT, REVOKE.)
Every query box below runs a real SQLite database in your browser - the same store schema from the stage overview. Edit any query, press Run, and experiment. Reset data restores the starting rows, so you cannot break anything.
DML: changing rows
INSERT - add rows
INSERT adds new rows. List the columns, then the values:
UPDATE - change existing rows
UPDATE changes rows that match its WHERE:
DELETE - remove rows
DELETE removes rows that match its WHERE:
UPDATE and DELETE hit every row that matches the WHERE - and with no WHERE, that is the whole table. DELETE FROM orders; empties it. Before running either, run the same WHERE as a SELECT first and check the rows are the ones you mean. (Here, Reset data saves you - in production nothing does.)
TRUNCATE - empty a table fast
TRUNCATE TABLE orders; removes all rows in one fast operation - quicker than DELETE because it does not process rows one by one.
TRUNCATE is standard in PostgreSQL, MySQL, and SQL Server, but SQLite has no TRUNCATE - you write DELETE FROM orders; instead (SQLite optimizes that case internally). The sandbox here is SQLite, so try the DELETE form. This is your first taste of a real lesson: SQL is a standard, but every engine has its own dialect.
TCL: controlling the transaction
Recall from the ACID section that a transaction is a group of changes treated as one unit. TCL is how you draw that boundary yourself.
BEGINstarts a transaction.COMMITmakes every change sinceBEGINpermanent.ROLLBACKthrows them all away, as if they never happened.
ROLLBACK - undo everything since BEGIN
Run this. The first count is 0 (rows gone inside the transaction); after ROLLBACK the second count is back to 3:
This is the safety net behind money transfers: if anything goes wrong mid-way, ROLLBACK leaves the data exactly as it was.
SAVEPOINT - a partial undo point
A SAVEPOINT is a named marker inside a transaction. ROLLBACK TO that savepoint undoes the work after it while keeping the work before it. Useful for "try this risky step; if it fails, back out just that part."
Customer 3 keeps its new IE; the blanket update after the savepoint is gone.
What can actually be rolled back?
ROLLBACK undoes a transaction - but which statements obey it depends on the engine.
DML always rolls back. INSERT, UPDATE, and DELETE are undone cleanly by ROLLBACK in every database. That is the whole point of a transaction.
DDL is where engines disagree. DDL (Data Definition Language - CREATE, ALTER, DROP) changes the database's structure, and not every engine can undo that inside a transaction:
- Transactional DDL (PostgreSQL, SQL Server, SQLite): a
CREATE/ALTER/DROPinside a transaction is rolled back like anything else. - Auto-committing DDL (Oracle, MySQL): issuing DDL silently commits the current transaction first, so it cannot be undone.
Our sandbox is SQLite, which has transactional DDL. Create a table inside a transaction, roll back, and it never existed:
A few operations are irreversible everywhere - DROP DATABASE is gone the moment it runs, transaction or not.
Transactional behaviour across databases (2026)
The same statement can be safe to roll back in one engine and a point of no return in another. Know your engine before you rely on it.
| Statement | PostgreSQL 18 | SQL Server 2022 | SQLite 3.49 | MySQL 8.4 | Oracle 23ai |
|---|---|---|---|---|---|
INSERT / UPDATE / DELETE | rolls back | rolls back | rolls back | rolls back | rolls back |
TRUNCATE | rolls back | rolls back | not supported* | auto-commits | auto-commits |
CREATE / ALTER / DROP | rolls back | rolls back | rolls back | auto-commits | auto-commits |
*SQLite has no TRUNCATE; use DELETE FROM, which is fully transactional.
The takeaway: on PostgreSQL you can wrap a risky migration (table changes included) in one transaction and roll the whole thing back on error. On MySQL or Oracle, a DDL statement commits everything before it - so a failed migration can leave you half-changed.
Exercise
Use the sandbox - Reset data between attempts.
1. Worked. Give every Irish customer a new country code, then undo it without committing.
BEGIN;
UPDATE customers SET country = 'IE-NEW' WHERE country = 'IE';
ROLLBACK;
SELECT * FROM customers; -- unchanged
2. Finish it. Insert a new product, then make it permanent. Fill the blanks.
BEGIN;
INSERT INTO products (id, name, price) VALUES (4, 'Cap', 15.00);
____;
SELECT * FROM products;
3. Write it yourself. Inside one transaction: delete all order_items for order 101, set a SAVEPOINT, delete order 101 itself, then ROLLBACK TO the savepoint so only the items stay deleted. Commit.
Show answers
2. COMMIT; makes the insert permanent.
3.
BEGIN;
DELETE FROM order_items WHERE order_id = 101;
SAVEPOINT after_items;
DELETE FROM orders WHERE id = 101;
ROLLBACK TO after_items;
COMMIT;
Quick quiz
DML and TCL
4 questions1Which statement is TCL, not DML?
2You ran DELETE inside a transaction and realised it was wrong, before COMMIT. What undoes it?
3Why can TRUNCATE be faster than DELETE for emptying a table?
4What does SAVEPOINT give you that COMMIT and ROLLBACK alone do not?
You can query, aggregate, join, nest subqueries, change data, and control transactions - and run it all yourself. Next is Stage 2 - Designing a Database, where you decide what the tables should be.