Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

START TRANSACTION;

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

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

COMMIT;

Is this possible?

share|improve this question

1 Answer 1

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

 
discard

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.