Bug #15037 | if statement does not work as documented in 5.0 manual | ||
---|---|---|---|
Submitted: | 17 Nov 2005 23:50 | Modified: | 18 Nov 2005 16:22 |
Reporter: | Aimee Grimes | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0 | OS: | Linux (Red Hat 4.0) |
Assigned to: | CPU Architecture: | Any |
[17 Nov 2005 23:50]
Aimee Grimes
[18 Nov 2005 16:22]
Hartmut Holzgraefe
the IF statement can only be used within procedures/functions
[16 Dec 2007 7:21]
Adam Dorian
So how to run the following type of logic in a schema_update.sql file which will be run each time an application is deployed (but only run when applicable due to conditional logic)? The following throws an error - but can someone tell me the correct way to run this in MySQL SQL script? IF((SELECT COUNT(*) AS column_exists FROM information_schema.columns WHERE table_name = 'my_table' AND column_name = 'old_column_name') > 0) THEN ALTER TABLE `my_table` CHANGE COLUMN `old_column_name` `old_column_name` VARCHAR(100) END IF
[28 Dec 2007 18:55]
Ron Dorfman
Does anyone have an answer? I have the same problem
[2 Jan 2008 23:22]
Joseph Wilwayco
According to the 2nd poster, IF only works in StoredProcs/Functions. So create a stored proc in a script, then call it at the end of the script! DELIMITER $$ DROP PROCEDURE IF EXISTS upgrade_database $$ CREATE PROCEDURE upgrade_database() BEGIN -- INSERT NEW RECORD IF PREEXISTING RECORD DOESNT EXIST IF((SELECT COUNT(*) AS column_exists FROM information_schema.columns WHERE table_name = 'test' AND column_name = 'test7') = 0) THEN ALTER TABLE test ADD COLUMN `test7` int(10) NOT NULL; UPDATE test SET test7 = test; SELECT 'Altered!'; ELSE SELECT 'Not altered!'; END IF; END $$ DELIMITER ; CALL upgrade_database();
[2 Jan 2008 23:25]
Joseph Wilwayco
Oh, and Adam. Thanks for the 'workaround' for doing a conditional alter-table statement...
[18 Jul 2011 13:51]
Paul Poulain
I've just submitted a feature request for this: http://bugs.mysql.com/bug.php?id=61895
[18 Jul 2011 14:19]
Paul Poulain
Oh, but as another workaround, you can do conditional insert/update statements as follows: INSERT INTO my_table(name, description) SELECT 'new_name', 'description' FROM DUAL WHERE NOT EXISTS (SELECT 'X' FROM my_table WHERE name = 'new_name); Maybe this is slightly less of a bodge(?) but will only work for insert/update statements, but not for other stuff (like "if (...) drop table...").