How to rename a column name in maria DB

Question

I am new to SQL, I was trying to change column name in my database's table. I am using 'xampp' with 'maria DB' (OS - Ubuntu 18.04)

I tried all of the followings:

ALTER TABLE subject RENAME COLUMN course_number TO course_id;
ALTER TABLE subject CHANGE course_number course_id;
ALTER TABLE subject CHANGE 'course_number' 'course_id';
ALTER TABLE subject  CHANGE COLUMN 'course_number'  course_id varchar(255);
ALTER TABLE subject CHANGE 'course_number' 'course_id' varchar(255);

But the only output I got was:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'column course_number to course_id' at line 1

Could someone please tell me what is the correct answer. I have no idea what to do further.

Solution 1

Table names, column names, etc, may need quoting with backticks, but not with apostrophes (') or double quotes (").

ALTER TABLE subject
    CHANGE COLUMN `course_number`   -- old name; notice optional backticks
                   course_id        -- new name
                   varchar(255);     -- must include all the datatype info

Solution 2

Starting with MariaDB 10.5.2 you should be able to do

ALTER TABLE subject RENAME COLUMN course_number TO course_id;

see https://mariadb.com/kb/en/alter-table/#rename-column

Comments

[Jake Lee]

Possible duplicate of Change column name in MariaDB

[ToujouAya]

You should change the symbol from ' to this `

[Kaveen Hyacinth]

thank you @ToujouAya. That was the mistake.

[Kaveen Hyacinth]

@JakeSteam It is a different from my question. He has messed up ' . ' with table name, in my case I was using the wrong symbol through out all the program.

[Michael restore Monica Cellio]

MariaDB [socialnet]> ALTER TABLE users RENAME COLUMN current_lat TO was_lat;<br/> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLUMN current_lat TO was_lat' at line 1

[Robert Hickman]

@Michael, maybe you’re using the wrong version? You can show your version with SELECT VERSION()

[Michael restore Monica Cellio]

10.3.22-MariaDB-1:10.3.22+maria~xenial-log

[Michael restore Monica Cellio]

I ended up using change column.

[Robert Hickman]

@MichaelrestoreMonicaCellio, yeah your version doesn't support the newer RENAME COLUMN way of doing it.