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%'