×

sql · level 3 · unit 5 · lesson 3

  1. meet it
  2. guess first
  3. your turn
  4. fix it
  5. nailed it

Tap a green word if you get lost.

meet it

marks a spot you can roll back to.

A long job might have one bad step among many good ones. ROLLBACK would throw away the lot. A savepoint lets you undo only part: SAVEPOINT name marks a spot inside the transaction.

undoes everything since that spot and keeps what came before. removes the mark and keeps the changes. The final COMMIT or ROLLBACK still decides for everything.

Here the arcade restocks stickers, then tries a teddy price rise. The rise is a mistake, so only that part goes.

BEGIN;
UPDATE prizes SET stock = stock + 20 WHERE prize = 'stickers';
SAVEPOINT price_rise;
UPDATE prizes SET cost = 99 WHERE prize = 'teddy';
ROLLBACK TO price_rise;
COMMIT;

SELECT prize, cost, stock FROM prizes ORDER BY prize;

After ROLLBACK TO, the savepoint is still there, so you can try again from the same spot.