Alter schema doesn't work

Question

I have to add the alter command to my schema.

SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'product'
        AND table_schema = DATABASE()
    AND column_name = 'date_madified'
    ) > 0,
    "SELECT 1;",
    "ALTER TABLE `product` CHANGE `date_madified` `date_modified`;"
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The above executes without giving error . But the column name doesnt get altered. When I tried to check the error I found below are the causes. 1. Proper database doesn't get selected. 2. 'IF' doesn't get gives correct result, even though I hardcode the database name.

Though sql in above format executes successfully for all other cases of modifying the name, data type and size etc, but in this case ony it doesn't work properly.

Answer 1

Obviously try the following on test data NOT in a production environment.

It seems you have your IF statement the wrong way around so are only executing select 1; when you find a field you want to modify. You need to change the order of the clauses in the IF statement.

Also the alter table statement requires a column definition, in your case something like

ALTER TABLE `product` CHANGE `date_madified` `date_modified` timestamp

Change the above to reflect the field type of your date_madified field.