MySQL IF Statement - script with error

I am a creating a MySQL updating script which will allow me to merge all my sql changes to several databases using a timestamp to determine which changes have already been updated.

I have set @cv to the current version of the database. The problem occurs in the if statement. What ever I change the IF statement to it causes if to fall over.

SET @cv = (SELECT `value` FROM `configs` WHERE `name` = 'cvDate');

IF @cv <= STR_TO_DATE("2010/11/10 12:15:00") THEN
  ALTER TABLE `feeds` ADD `tone` VARCHAR( 255 ) NOT NULL AFTER `type` ,
  ADD `authority` DECIMAL( 2, 1 ) NOT NULL DEFAULT '0.0' AFTER `t开发者_Python百科one`;
END IF;

UPDATE `configs` SET `value` = NOW() WHERE `name` = "cvDate";

The error I'm getting is :

#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 @cv <= STR_TO_DATE("2010/11/10 12:15:00") THEN   ALTER TABLE `feeds` ADD `to' at line 1 

Any help would be greatly appreciated.

The problem is that the IF control flow construct only works in stored procedures (Just like all other Flow Control Constructs... So you'd need to define a procedure to do what you're asking...

So here's how to define sample procedure to do what you want:

DELIMITER //
CREATE PROCEDURE myProcedure()
BEGIN
    SET @cv = (SELECT `value` FROM `configs` WHERE `name` = 'cvDate');

    IF @cv <= STR_TO_DATE("2010/11/10 12:15:00") THEN
        ALTER TABLE `feeds` ADD `tone` VARCHAR( 255 ) NOT NULL AFTER `type` ,
          ADD `authority` DECIMAL( 2, 1 ) NOT NULL DEFAULT '0.0' AFTER `tone`;
    END IF;

    UPDATE `configs` SET `value` = NOW() WHERE `name` = "cvDate";
END//
DELIMITER ;

That will create it, then to run it, just do:

CALL myProcedure();

See the docs for more info.

You can actually even make it take parameters so you don't need to hard-code everything. But I'm not sure how you would do that based upon what you provided (I'm not sure what exactly you're trying to accomplish)...

If you want to make it take a sql statement as a parameter (using Prepared Statements ):

DELIMITER //
CREATE PROCEDURE myInputProcedure(IN updateDate DATETIME, IN sql TEXT)
BEGIN
    SET @cv = (SELECT `value` FROM `configs` WHERE `name` = 'cvDate');

    IF @cv <= updateDate THEN
        PREPARE stmt1 FROM sql;
        EXECUTE stmt1;
    END IF;

    UPDATE `configs` SET `value` = NOW() WHERE `name` = "cvDate";
END//
DELIMITER ;

Then, simply call it like this:

@sql = 'ALTER TABLE `feeds` ADD `tone` VARCHAR( 255 ) NOT NULL AFTER `type` ,
          ADD `authority` DECIMAL( 2, 1 ) NOT NULL DEFAULT '0.0' AFTER `tone`;';
@date = STR_TO_DATE("2010/11/10 12:15:00");
CALL myInputProcedure(@date, @sql);

(Note that I added @sql and @date for readability, not because they are necessary)...

You can use flow control constructs only within stored procedures or functions. proof link

Just give this a try.

Instead of this:

SET @cv = (SELECT `value` FROM `configs` WHERE `name` = 'cvDat');

Make it like this:

SELECT  @cv = `value` FROM `configs` WHERE `name` = 'cvDat';

Not sure for MySql, but this is what I do in MS SQL.