0

I never thought about the implication and I created a column named "add" in mysql. (obviously it was not in my mind while creating the table that add is a reserved word) While giving every query this is turning out to be a problem. I've decided to change the name and now I can't get to change it either.

I tried the following variations: none worked

mysql> alter table ml_n1 m modify column m.add addmovie tinyint(4);                                 
ERROR 1064 (42000): 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 'm modify column m.add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 modify column add addmovie tinyint(4);
ERROR 1064 (42000): 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 'add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 modify column 'add' 'addmovie tinyint(4)';
ERROR 1064 (42000): 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 ''add' 'addmovie tinyint(4)'' at line 1

mysql> alter table ml_n1 modify column 'add' addmovie tinyint(4);
ERROR 1064 (42000): 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 ''add' addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 change add addmovie tinyint(4);
ERROR 1064 (42000): 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 'add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 change 'add' addmovie tinyint(4);
ERROR 1064 (42000): 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 ''add' addmovie tinyint(4)' at line 1

Could someone suggest a workaround?

rk567
  • 289
  • 1
  • 4
  • 16

4 Answers4

2
ALTER TABLE ml_n1 CHANGE COLUMN `add` addmovie TINYINT(4)

As you have hinted at; calling a column add is problematic because add is a reserved word in MySQL, in order to reference the column you need to escape it with backticks, `.

You need CHANGE rather than MODIFY to change a column's name.

N.B. From the docs:

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE.

I wouldn't call this a workaround.

Arth
  • 12,789
  • 5
  • 37
  • 69
2
alter table ml_n1 change `add` addmovie tinyint(4);
Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41
2

The following should work for you:

ALTER TABLE `ml_n1` CHANGE COLUMN `add` `addmovie` tinyint(4);

Back-ticks allow you to reference column names that contain reserved words or spaces etc.
I personally make it a habit to place backticks around table and column references when coding with MySQL.

AeroX
  • 3,387
  • 2
  • 25
  • 39
2

Use backticks around the column name

    mysql>alter table m1_n1 change `add` addmovie tinyint(4);
Brian Ridgeway
  • 255
  • 2
  • 5