CODEWITHSUNDEEP
sqlitecommit
Wolfpack'08
Wolfpack'08

Reputation: 4128

Cancel a transaction or part of a transaction before commit?

I have a long transaction that I have began using BEGIN, and I made a mistake about 3 lines prior to the last line. Is there a way to null that line?

Here's an example:

BEGIN TRANSACTION;
CREATE TABLE purchase(id integer PRIMARY KEY, purchasePrice integer CHECK(purchasePrice>0), customer text);
INSERT INTO purchase(purchasePrice, customer) VALUES(1200, "Jerome");
INSERT INTO purchase(purchasePrice, customer) VALUES(200, "Jeffery");
INSERT INTO purchase(purchasePrice, customer) VALUES(40, "Craig");
INSERT INTO purchase(purchasePrice, customer) VALUES(1640, "Donovan");
INSERT INTO purchase(purchasePrice, customer) VALUES(100, "Williams");
INSERT INTO purchase(purchasePrice, customer) VALUES(50, "Matsushi");
INSERT INTO purchase(purchasePrice, customer) VALUES(150, "Xhao");
INSERT INTO purchase(purchasePrice, customer) VALUES(250, "Dilberta");
INSERT INTO purchase(purchasePrice, customer) VALUES(840, "Brow"); //Supposed to be 9440,"Brown"
INSERT INTO purchase(purchasePrice, customer) VALUES(440, "DarkBrown");
INSERT INTO purchase(purchasePrice, customer) VALUES(20, "Black");
COMMIT;

I do understand that I can later update the affected row.

Another example situation:

BEGIN TRANSACTION;
CREATE TABLE Movies(MId integer primary key autoincrement, Title text);
INSERT INTO Movies VALUES(1,'Capote');
INSERT INTO Movies VALUES(2,'Scent of a woman');
INSERT INTO Movies VALUES(3,'Stigmata');
INSERT INTO Movies VALUES(4,'Exorcist');
INSERT INTO Movies VALUES(5,'Hamsun');
//Changed my mind and want to name the table "movie" and  name the columns "m_id" and "title".

So, I'm wondering a) how to cancel; and b) is it possible to go up a few lines, and if so, how?

Upvotes: 1

Views: 1605

Answers (2)

CL.
CL.

Reputation: 180040

If you had created savepoints, you would be able to roll back part of the transaction. (But that would be even more typing.)

When you are doing many changes, it is a good idea to write all the SQL commands into a text file before you execute them.

Upvotes: 1

Wolfpack'08
Wolfpack'08

Reputation: 4128

The ROLLBACK feature cancels the complete transaction. I have not found a way to go back line-by-line, so the UPDATE or ALTER commands can be used for tables or fields/rows, respectively.

Upvotes: 0

Related Questions