Undo only the mistake

One change in a transaction was right and the next was a mistake. Keep the first and undo the second.

What to build

A transaction that keeps the food price rise and undoes the mistaken price change, using a savepoint.

Requirements

• Keep the starter's two UPDATE statements in the same order.
• Add a savepoint between them, roll back to it after the second UPDATE, then release it and commit.
• Show every column of products, in order of id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER NOT NULL
);

Output

Columns id, name, category and price, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Prices are whole numbers. The table may be empty.

Example

When a test starts with:

INSERT INTO products VALUES (1, 'Rice', 'food', 60), (2, 'Pen', 'office', 10), (3, 'Milk', 'food', 90);

your SQL should print:

id  name  category  price
--  ----  --------  -----
1   Rice  food      70
2   Pen   office    10
3   Milk  food      100
(3 rows)

Why it works

ROLLBACK TO undoes only the changes made after the savepoint and leaves the transaction open, so the food price rise survives and is made permanent by COMMIT.

Examples

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER NOT NULL
);
INSERT INTO products VALUES (1, 'Rice', 'food', 60), (2, 'Pen', 'office', 10), (3, 'Milk', 'food', 90);

id  name  category  price
--  ----  --------  -----
1   Rice  food      70
2   Pen   office    10
3   Milk  food      100
(3 rows)

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER NOT NULL
);
INSERT INTO products VALUES (1, 'Bag', 'office', 900);

id  name  category  price
--  ----  --------  -----
1   Bag   office    900
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

BEGIN;
UPDATE products SET price = price + 10 WHERE category = 'food';
SAVEPOINT before_sale;
UPDATE products SET price = price * 10;
ROLLBACK TO before_sale;
RELEASE before_sale;
COMMIT;

SELECT * FROM products ORDER BY id;

Lesson: Transactions and concurrency →