Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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.

share|improve this answer
Like Andomar said you may have simplified your example: but in simple cases you can use the affected_rows function (in a stored procedure or client side) to get the number of rows the INSERT query did. You may not need to do count(*). – Jannes Jun 25 '12 at 16:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.