How to properly alter table default value

Question

I can't figure out why my alter table query throws an error. Currently, this is a (DATETIME) column with default value NULL.

My wish is to alter it so datetime value gets automatically populated when I update a row. I'm trying to write an alter statement, but I can't figure out why mine is throwing the error.

My alter statement

ALTER TABLE `mydb`.`orders` CHANGE COLUMN `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP '{}';

And this is the error that I'm getting

16:28:34    ALTER TABLE `mydb`.`orders` CHANGE COLUMN `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP '{}'  Error Code: 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 'NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP '{}'' at line 1  0.00041 sec

I'm using MySQL version 5.7

Answer 1

The CHANGE COLUMN alteration is used when you might want to change the name of the column, and requires you to provide the new name after the old name. If you're not renaming the column, you have to provide the name twice. Your command tries to rename the date_u column to DATETIME, and it's missing the datatype before the NULL keyword.

Use MODIFY COLUMN instead. It's the same, but doesn't allow renaming, so doesn't require you to give the column name twice.

ALTER TABLE `mydb`.`orders` MODIFY COLUMN `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;

I'm also not sure what you intended with '{}' at the end, but I don't think it's valid syntax, either, so I've removed it.

Answer 2

You're missing to name the new column.

change this:

ALTER TABLE `mydb`.`orders`
CHANGE COLUMN `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP '{}';

into this:

ALTER TABLE `mydb`.`orders`
CHANGE COLUMN `date_u` `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;

Notice the column name is typed twic, because you want the column name to stay the same, formerly here's the change column syntax:

ALTER TABLE `table_name`
CHANGE COLUMN `column_name` `column_new_name` (...);

Or, you can just you modify column syntax:

ALTER TABLE `mydb`.`orders` MODIFY COLUMN `date_u` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;

Ps: I don't get what you mean by '{}' so I removed it because I think it's not a valid syntax.

Hope I pushed you further.