Practise SQL UPDATE Safely with a Transaction and ROLLBACK
On this page
UPDATE changes stored data, so learning it needs a different workflow from a read-only SELECT. This exercise uses a disposable SQLite practice database and deliberately rolls the change back. Do not run it against production, shared or important records.
Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.
Preview the exact target
We will temporarily change order 102 from 800 to 900. First run the same identifying condition in a SELECT:
SELECT order_id, customer_id, amount
FROM orders
WHERE order_id = 102;Confirm that exactly one row appears and its current amount is 800. order_id is a primary key, so it identifies one order. A broader condition such as customer_id could affect several rows in a different dataset.
Start an explicit transaction
Run these statements in the same SQLite connection. A transaction groups changes into a unit that can be committed or rolled back.
BEGIN TRANSACTION;
UPDATE orders
SET amount = 900
WHERE order_id = 102;
SELECT changes() AS updated_rows;
SELECT order_id, amount FROM orders WHERE order_id = 102;
ROLLBACK;
SELECT order_id, amount FROM orders WHERE order_id = 102;The updated-row count should be 1. Inside the transaction, the order shows 900. After ROLLBACK, it shows 800 again. The final SELECT is important: attempting an undo is not the same as verifying its result.
Understand the two ways to finish
COMMIT saves the transaction's changes. ROLLBACK undoes changes in the current transaction. This lesson uses ROLLBACK because its purpose is practice. Once a transaction is committed, a later ROLLBACK does not reverse that historical commit.
Database tools differ in connection handling and automatic commits. If you run each statement in a separate session, the example may not behave as intended. Confirm your tool supports a persistent transaction and use a fresh disposable database if you are unsure.
Why WHERE deserves a separate review
An UPDATE without WHERE targets every row in the table. A wrong WHERE can also change the wrong subset. Previewing a SELECT with the same condition, checking the expected count and keeping a transaction open for inspection are complementary safeguards.
Even a one-row count is not enough by itself: it could be the wrong order. Compare the key and current value with the intended record. In a real concurrent system, additional checks may be needed to avoid overwriting another person's change.
Handle an error without guessing
If you see an error, inspect whether the transaction is still active and use your tool's supported rollback procedure. Do not repeatedly run the UPDATE hoping the message disappears. Repeated relative changes, such as adding 100, can compound the result.
For important data, follow the organisation's approved backup, review and deployment process. A transaction is useful protection, but it does not replace authorisation, correct business rules or a tested recovery plan.
Try it yourself
In the disposable database, temporarily change order 103 to 650, inspect it and roll back. What should the final amount and full-table total be?
Check your answer
After rollback, order 103 must be 600 and the full-table total must still be 4100. Use the same transaction pattern with WHERE order_id = 103, then run a final SELECT and SELECT SUM(amount) FROM orders;.
If the final amount remains 650, the change was not rolled back as intended. Stop and inspect the tool's transaction behaviour rather than continuing with a practice database whose starting state is now different.
Keep exploring: SQL learning path.