You can create SAVEPOINTs to allow you to revert to a certain point within a TRANSACTION. In a transaction, you can ROLLBACK to multiple savepoints. Once you reach the desired state, you can COMMIT it to end the transaction. If you ROLLBACK without referencing a savepoint, it'll rollback the entire transaction and end the transaction. Lines 1-13 show a transaction with multiple savepoints, and then reverting to multiple savepoints before COMMITing and ending the transaction. Lines 17-54 show the same transaction with line by line notes saying what it's doing.
START TRANSACTION; UPDATE Orders4 SET Price=1.00 WHERE OrderID=27; SAVEPOINT savepoint1; UPDATE Orders4 SET Price=2.00 WHERE OrderID=27; SAVEPOINT savepoint2; UPDATE Orders4 SET Price=3.00 WHERE OrderID=27; SAVEPOINT savepoint3; UPDATE Orders4 SET Price=4.00 WHERE OrderID=27; SELECT * from Orders4; ROLLBACK TO savepoint3; ROLLBACK TO savepoint2; ROLLBACK TO savepoint1; COMMIT; -- DETAIL OF LINES 1-12!!! -- START TRANSACTION, DISABLE AUTO-COMMIT START TRANSACTION; -- UPDATE Price TO 1.00 WHERE OrderID=27 UPDATE Orders4 SET Price=1.00 WHERE OrderID=27; -- CREATE savepoint1 WITH VALUE 1.00 SAVEPOINT savepoint1; -- UPDATE Price TO 2.00 WHERE OrderID=27 UPDATE Orders4 SET Price=2.00 WHERE OrderID=27; --CREATE savepoint2 WITH VALUE 2.00 SAVEPOINT savepoint2; -- UPDATE Price TO 3.00 WHERE OrderID=27 UPDATE Orders4 SET Price=3.00 WHERE OrderID=27; --CREATE savepoint3 WITH VALUE 3.00 SAVEPOINT savepoint3; --UPDATE Price TO 4.00 WHERE OrderID=27 UPDATE Orders4 SET Price=4.00 WHERE OrderID=27; -- SELECT TO SHOW THE RESULTS, WHICH WILL SHOW THE VALUE AS 4.00 SELECT * from Orders4; -- ROLLBACK TO savepoint3, REVERTS VALUE BACK TO 3.00 ROLLBACK TO savepoint3; --ROLLBACK TO savepoint2, REVERTS VALUE BACK TO 2.00 ROLLBACK TO savepoint2; -- ROLLBACK TO savepoint1, REVERTS VALUE BACK TO 1.00 ROLLBACK TO savepoint1; -- COMMIT TO FINALIZE THE VALUE AS 1.00, THEREBY ENDING THE TRANSACTION COMMIT;