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.

Possible duplicate of [Change column name in MariaDB](https://stackoverflow.com/questions/28507987/change-column-name-in-mariadb) — Jake Lee, Dec 12 '18 at 02:42
@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. — Kaveen Hyacinth, Dec 12 '18 at 03:04

Rick James · Accepted Answer · 2019-06-18T13:28:42.710

55

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

edited Jun 18 '19 at 13:28

answered Dec 16 '18 at 04:32

Rick James

135,179
13
127
222

score 30 · Answer 2 · answered Mar 10 '20 at 20:49

30

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

answered Mar 10 '20 at 20:49

Robert Hickman

997
1
11
22

MariaDB [socialnet]> ALTER TABLE users RENAME COLUMN current_lat TO was_lat;
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 – Michael restore Monica Cellio Feb 07 '21 at 10:09
2

@Michael, maybe you’re using the wrong version? You can show your version with `SELECT VERSION()` – Robert Hickman Feb 07 '21 at 18:32
10.3.22-MariaDB-1:10.3.22+maria~xenial-log – Michael restore Monica Cellio Feb 08 '21 at 00:00
1

I ended up using `change column`. – Michael restore Monica Cellio Feb 08 '21 at 00:01
1

@MichaelrestoreMonicaCellio, yeah your version doesn't support the newer `RENAME COLUMN` way of doing it. – Robert Hickman Feb 08 '21 at 18:30

score 7 · Answer 3 · edited Jan 09 '22 at 04:05

7

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;

edited Jan 09 '22 at 04:05

Ihor Konovalenko

1,298
2
16
21

answered Jan 08 '22 at 06:24

kunal kanse

71
1
1

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 :) – waltmagic Dec 15 '22 at 16:16

How to rename a column name in maria DB

3 Answers3

Linked

Related