5

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

Mehrdad Pedramfar
  • 10,941
  • 4
  • 38
  • 59
Valor_
  • 3,461
  • 9
  • 60
  • 109

2 Answers2

2

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I was searching over stackoverflow and answers had this '{}' in alter statements. I also don't know what this is and I'm curious. This is working! Thank you! – Valor_ Sep 08 '18 at 14:59
  • @Valor_ Can you give a link to such an answer? Are you sure it wasn't part of a Python format string? – Barmar Sep 08 '18 at 15:01
  • here https://stackoverflow.com/questions/11312433/how-to-alter-a-column-and-change-the-default-value – Valor_ Sep 08 '18 at 15:09
  • 1
    That's trying to make the default value be the string `{}`. – Barmar Sep 08 '18 at 15:11
1

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.

ThS
  • 4,597
  • 2
  • 15
  • 27