SQL SAVEPOINT
Roll back part of a transaction without discarding earlier successful work.
What & Why
A savepoint is a named position inside a transaction that ROLLBACK TO SAVEPOINT can restore. It supports controlled recovery during multi-step operations while keeping valid earlier changes pending.
See How It Works
BUSINESS QUESTION
Keep a reviewed campaign status change while undoing a later optional adjustment.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
BEGIN;
UPDATE marketing.campaigns SET status = 'paused' WHERE id = 1;
SAVEPOINT campaign_paused;
UPDATE marketing.campaigns SET spend = spend * 1.10 WHERE id = 1;
ROLLBACK TO SAVEPOINT campaign_paused;
COMMIT;SAVEPOINT — partially undo campaign 1 changes
| step | campaign_id | status | spend |
|---|---|---|---|
| BEGIN | 1 | active | 55000.00 |
| SAVEPOINT campaign_paused | 1 | paused | 55000.00 |
| UPDATE after savepoint | 1 | paused | 60500.00 |
| ROLLBACK TO campaign_paused | 1 | paused | 55000.00 |
| COMMIT | 1 | paused | 55000.00 |
ROLLBACK TO removes only the 10% spend increase made after the savepoint; the earlier status change for campaign ID 1 remains and is committed.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario