Zerethon IDE
Sign in

Transactions

Group statements with BEGIN and COMMIT, or undo them with ROLLBACK.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗
main.sql 
BEGIN;
UPDATE students SET year = year + 1;
DELETE FROM enrollments;
ROLLBACK;   -- undo both

SELECT name, year, (SELECT count(*) FROM enrollments) AS enrollments
FROM students WHERE id = 4;
setup/schema.sql 
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

nameyearenrollments
Linus15
Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments.
  • Between BEGIN and COMMIT, either every change is kept or — with ROLLBACK — none.
  • Outside a transaction each statement is committed on its own.
  • In the playground a failed statement stops the run; the database is thrown away anyway, so practise ROLLBACK explicitly as above.

Related topics

Official references