mardi 19 juillet 2016

ALTER TABLE IF EXISTS alternative and error

We are writing a MySQL query to rename a table if it exists, else do something else. Following the post here: Alter table if exists or create if doesn't

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = 'animal' AND TABLE_NAME='animals_testing') 

THEN

    ALTER TABLE animals_testing RENAME TO animals;

END IF;

Sadly, an error message keeps popping out:

/* SQL Error (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 EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCH' at line 1 */

However, the individual query works fine:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = 'animal' AND TABLE_NAME='animals_testing

Could any guru enlighten? Or is there an alternative way to do "RENAME TABLE IF EXISTS...ELSE...."? Thank you!

Aucun commentaire:

Enregistrer un commentaire