Can I rollback/abort a transaction if no rows were inserted?

Posted on

Question :

I have a transaction in which I want to do something like this:


INSERT INTO table1 (...)
SELECT ... FROM table 2;

... if select count(*) from table1 returns 0 abort/rollback ...


Is this possible?

Answer :

Sure. In MySQL, you can only use if in stored procedures, so this is typically done client side. Pseudo-code:

int rows = db.ExecuteScalar(
    "start transaction; " +
    "insert into table1 (col1) select col1 from table2; " +
    "select count(*) from table1");
if (rows == 0) {
    db.ExecuteNonQuery("abort transaction");
} else {
    db.ExecuteNonQuery("commit transaction");

Rolling back a transaction that did nothing (inserted no rows) doesn’t make a whole lot of sense, but I’m assuming you simplified reality for this question.

Leave a Reply

Your email address will not be published. Required fields are marked *