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.

Chasechaser

Possible duplicate of Change column name in MariaDB – Huffish

You should change the symbol from ' to this ` – Tripp

thank you @ToujouAya. That was the mistake. – Chasechaser

@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. – Chasechaser

Answer 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

Ameliaamelie

Answer 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

Taste

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 – Doughy

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

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

I ended up using change column. – Doughy

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

Answer 3

alter table "table_name" change column "old_name" "New_name" "datatype"*;

there is no need to use "TO" between old_name & New_name , datatype of New_name is must

e.g. -

alter table student change column id roll_no int;

Warble

Thank you for sharing this. I swore the documentation said to use 'TO' but after quadruple checking it doesn't have the 'TO' keyword. I was losing my mind! lol Thank you :) – Mercantile

Answer 4

ALTER TABLE subject CHANGE COLUMN course_number course_id VARCHAR(100);

Inversion

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Darmit