The title is rewritten as: Successfully solved - the problem of unable to update mySQL column names using the mysql command line

Question

I cannot change the column names in the mysql database I created.

I tried the following commands but none of them seem 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 I created, (oldcolumnname) is the original column name, and (newcolumnname) is the new column name.

Here is a simple to-do list I created for learning MySQL with the following items:

id	todo	completed
1	Ready to take off	is
2	Learn some MySQL	is
3	Remember that damn semicolon	No

In this case I tried changing the column 'todo' to 'To Do' or 'To-Do' but every time I try these commands I get the famous "Check your SQL version manual" ".

Any tips on what I might be doing wrong? Thanks!

I've looked at multiple tutorial sites and even looked at another StackOverflow question

Update The final solution was a two-step process. First, I need to use the TO phrase between the column names. Secondly, column names don't like special characters.

The query that finally worked was:

ALTER TABLE mytablename CHANGE COLUMN todo TO ToDo

Answer 1

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

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

If you really feel you must include spaces or dashes in column names, in MySQL you can use arbitrary identifiers, just enclose them in backticks:

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

But every time you reference the column in sql, you need to surround it with backticks:

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