$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Grouping changes with transactions #

Every INSERT, UPDATE, and DELETE you have written so far ran on its own. But sometimes several statements only make sense as a group — for example, moving money between two bank accounts needs both the withdrawal and the deposit to happen together. If only one of them succeeded, the data would be left in a broken state.

A transaction groups statements together so that either all of them take effect, or none of them do:

BEGIN;

UPDATE table_name
SET
    column_name = new_value
WHERE
    condition;

COMMIT;
  • BEGIN: Starts a new transaction.
  • COMMIT: Saves every change made since BEGIN, permanently.
  • ROLLBACK: Instead of COMMIT, this undoes every change made since BEGIN, as if none of it ever happened.

ROLLBACK is what makes transactions useful for safety: if something goes wrong partway through (or you simply change your mind), you can back out of every change at once instead of trying to manually undo each statement.

BEGIN;

UPDATE table_name
SET
    column_name = new_value
WHERE
    condition;

ROLLBACK;

Exercise #

Write a command that starts a transaction, changes the price of the product with id 1 to 999.99, and then rolls the transaction back, so that the product's price ends up exactly the same as it was before.

Tests #

  • Your command uses both BEGIN and ROLLBACK.
  • After running your command, the price of the product with id 1 is unchanged from its original value.
Solution 
BEGIN;

UPDATE products
SET
    price = 999.99
WHERE
    id = 1;

ROLLBACK;
Output will be displayed here