CODEWITHSUNDEEP
mysql
ninjaneer
ninjaneer

Reputation: 7031

Simple IF Statement in MySQL isn't working

I have a table with columns id and name.

START TRANSACTION;
UPDATE Test SET name='test' where id=1;
SELECT ROW_COUNT() INTO @affected_rows;
IF (@affected_rows > 0) THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF

It seems to be returning an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (affected_rows > 0) THEN COMMIT' at line 1 0 secs

Currently running MySQL 5.6.22

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

First, if as a control flow expression is only allowed in programming blocks -- stored procedures, triggers, and functions.

Second, the logic doesn't make sense. If no rows are affected, there is no need to rollback the transaction.

To point out the documentation:

The IF statement for stored programs implements a basic conditional construct.

The term "stored programs" means some programming block. Don't confuse this if with the MySQL function if(), which can be used in a query.

Upvotes: 2

Related Questions