6

I have the following MYSQL query:

START TRANSACTION;

SELECT sport_id INTO @a FROM sports WHERE sport_id = 2 FOR UPDATE;

UPDATE sports SET sport_name = 'Table Tennis' WHERE sport_id = @a;

if (@a > 1) then
    COMMIT;
ELSE
    ROLLBACK;
END IF;

The problem is that it returns an error at the if statement:

#1064 - 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 (@a > 1) then COMMIT' at line 1

I've looked on stack overflow and there is an answer showing a similar query, written in pretty much the same way, but they are using the variable without an @ symbol. Removing the @ for my query does not resolve the issue.

This is just a test query to try out some transactions using MYSQL, hence why the query seems a little pointless. I'm a little stuck.

Community
  • 1
  • 1
Luke
  • 22,826
  • 31
  • 110
  • 193

1 Answers1

5

MySQL doesn't recognize a statement beginning with the keyword IF as as a valid SQL statement.

The IF statement works only in the context of a compound statement (that is, a block of statements enclosed between BEGIN and END. Currently, the compound statement is only supported in the context of a stored program (stored procedure, function or trigger.)

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html

For testing, try...

DELIMITER //

CREATE PROCEDURE usp_test_transaction()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;

  -- whatever DML operations and SELECT statements you want to perform go here

  IF (1=1) THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END//

DELIMITER ;

CALL usp_test_transaction;

(NOTE: I am not advocating here that transactions be handled inside of stored procedure. My personal preference is to NOT do this, and instead handle transactions at a higher level. But the example above should work; I believe MySQL does support calling START TRANSACTION, COMMIT and ROLLBACK within the context of a stored procedure.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks for the comment. I've just tried putting a BEGIN END around the statement and it still comes up with the same error. Can you recommend where I am to put the BEGIN and END statements to resolve the problem? – Luke Apr 02 '13 at 14:23
  • The BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). http://dev.mysql.com/doc/refman/5.5/en/stored-programs-defining.html – spencer7593 Apr 02 '13 at 14:30
  • Or you could try using the case operator : http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html – Jakub Czaplicki Apr 02 '13 at 14:32