SQL from Zero › Module 6: TCL: keeping changes safe
Undoing with ROLLBACK
Change your mind before it's too late, and undo everything since BEGIN.
Lesson 29 of 33 · about 12 minutes
Video: The undo buttonThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
COMMIT keeps the changes. Its opposite is ROLLBACK, which throws away every change since BEGIN, as if they never happened:
BEGIN;
UPDATE books SET price = 0; -- Oops! Forgot the WHERE
SELECT title, price FROM books; -- Every price is 0...
ROLLBACK; -- ...and now they're all back
That's a safety net worth getting used to. When you're about to change a lot of data:
BEGIN- Make the change.
- Look at the result with a
SELECT. - If it's right,
COMMIT. If it's wrong,ROLLBACK.
Roll back when something goes wrong part-way. Gift card 99 doesn't exist, so here the second UPDATE changes nothing, without an error. If you COMMIT anyway, $50 disappears from Emma's card:
BEGIN;
UPDATE gift_cards SET balance = balance - 50 WHERE id = 1;
UPDATE gift_cards SET balance = balance + 50 WHERE id = 99; -- no such card!
ROLLBACK; -- so cancel the whole transfer
Apps do this automatically: if any step fails, they roll back instead of committing.
After COMMIT, it's too late. ROLLBACK only undoes the transaction that's still open.
This lesson uses the same gift cards as the last one:
| gift_cards.id | customer | balance |
|---|---|---|
| 1 | Emma Johnson (customer 1) | $50.00 |
| 2 | Oliver Brown (customer 4) | $20.00 |
| 3 | Yuki Tanaka (customer 5) | $35.00 |
| 4 | Sofia Rossi (customer 7) | $10.00 |
Key ideas
- ROLLBACK throws away every change since BEGIN.
- Check the result with a SELECT before you COMMIT, and roll back if it's wrong.
- After COMMIT, ROLLBACK can no longer undo those changes.
Practise
Loading practice…