Home > database >  SOLVED - Unable to update mySQL column name via mysql command line
SOLVED - Unable to update mySQL column name via mysql command line

Time:12-30

I cannot change the column name in a mysql database I created.

I have tried the following commands, and none of them appear to work.

alter table (mytablename) CHANGE COLUMN (oldcolumnname) (newcolumnname) varchar(120);

alter table (mytablename) RENAME COLUMN (oldcolumnname) (newcolumnname) varchar(120);

ALTER TABLE (mytablename) CHANGE (oldcolumnname) (newcolumnname) varchar(120);

Where (mytablename) is the name of the table that I created, (oldcolumnname) is the original column name, and (newcolumnname) is the new column name.

This is a simple to-do list I created to learn MySQL with the following items:

id todo completed
1 Prepare for Take Off Yes
2 Learn some MySQL Yes
3 Remember that damn semicolon No

In this case, I am trying to alter the column 'todo' to say either 'To Do' or 'To-Do' but every time I try these commands. I keep getting the famous "Check your SQL version manual".

Any hints as to what I might be doing wrong? TIA!

I have reviewed multiple tutorial websites and even reviewed another StackOverflow question

UPDATE The ultimate solution was two-fold. First, I needed to use the TO phrase between the column names. Second, the column names do not like special characters.

The query that ultimately worked was:

ALTER TABLE mytablename CHANGE COLUMN todo TO ToDo

CodePudding user response:

You should keep the column named todo; you can always change the output when you select like:

select id, todo as 'To Do', completed from ...

If you feel you really must include a space or - in the column name, in mysql you can use arbitrary identifiers that have not-usually allowed characters by enclosing them in backticks:

alter table ... rename column todo to `To Do`

but then every time you reference the column in sql you will need to enclose it in backticks:

select id,`To Do`,completed from ... where `To Do` like '%learn%'
  • Related