Rename a column in MySQL

Question

I am trying to rename a column in MySQL community server 5.5.27 using this SQL expression:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

I also tried

ALTER TABLE table_name RENAME old_col_name TO new_col_name;

But it says:

Error: check the Manual that corresponds to your MySQL server version

possible duplicate of [How to rename a table column in MySQL](http://stackoverflow.com/questions/4002340/how-to-rename-a-table-column-in-mysql) — Joel Hinz, May 17 '15 at 19:00

Rizky Fakkel · Accepted Answer · 2021-01-11T20:37:19.710

735

Use the following query:

ALTER TABLE tableName CHANGE oldcolname newcolname datatype(length);

The RENAME function is used in Oracle databases.

ALTER TABLE tableName RENAME COLUMN oldcolname TO newcolname datatype(length);

@lad2025 mentions it below, but I thought it'd be nice to add what he said. Thank you @lad2025!

You can use the RENAME COLUMN in MySQL 8.0 to rename any column you need renamed.

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

ALTER TABLE Syntax: RENAME COLUMN:

Can change a column name but not its definition.

More convenient than CHANGE to rename a column without changing its definition.

edited Jan 11 '21 at 20:37

answered May 17 '15 at 19:02

Rizky Fakkel

8,703
1
14
20

3

Can you have columns with the same name - or why does the datatype it was have to be specified? – Lealo Aug 16 '17 at 20:13
6

@Lealo As the command says, `CHANGE` changes the column on a table and its type. The MYSQL docs state: `Attributes present in the original definition but not specified for the new definition are not carried forward.`. Meaning you have to specify the table definitions or they will not be used for that column. If you only want to change a column's datatype, simply leave out the `newcolname` – Rizky Fakkel Aug 17 '17 at 13:32
2

@Flimm you're right again. Oracle docs; `In MySQL, the quote character is the backtick. If the SQL mode ANSI_QUOTES is set, double quotes can also be used to quote the identifiers. In Oracle, identifiers are quoted using double quotation marks.` Aaaand edited. I need coffee.. – Rizky Fakkel Oct 23 '17 at 09:49
@JorgeMachado It works just fine. I believe it is not my example that is creating errors for you. Check the syntax, make sure you are using the right example (Top for MySQL, bottom for OracleDB). – Rizky Fakkel Mar 05 '18 at 12:20
I tried it on my mysql-8.0.11-winx64 and it did not work. Worked when I left the quotes out completly: .....-> ALTER TABLE tableName CHANGE oldcolname newcolname datatype(length); – Benjamin Basmaci May 29 '18 at 16:13
1

Thank you @Smogen! That's odd. Backticks are supposed to be the default quote character for MySQL. Are you sure you didn't use `"` instead of `\``? – Rizky Fakkel Jun 04 '18 at 09:29
@RizkyFakkel absolutely sure. Maybe its OS dependent? – Benjamin Basmaci Jun 04 '18 at 13:12
I agree, but I'm assuming renaming a column doesn't happen often in bigger applications, and if they do they are probably tested on a test environment beforehand. Maybe MariaDB will come with an equivalent of `RENAME COLUMN x TO y` in the future. – Rizky Fakkel Feb 26 '19 at 09:50
3

In MySQL (`MYSQL STATUS` shows `mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper`), I get `Error 1064 (42000): You have an error in your SQL Syntax;...near 'COLUMN thread_id TO threadId'...` for the query `ALTER TABLE comment RENAME COLUMN thread_id TO threadId;`. I also tried adding backticks `\``. I ended up using `CHANGE`. – Reed Aug 02 '19 at 16:24
1

Does this operation delete constraints on the column? EDIT: after checking `information_schema.key_column_usage`, it seems the column name in constraints is updated with this operation. – sisisisi Nov 06 '19 at 22:31
Back ticks are not needed usually in MySQL unless the table or column name or function name, etc. is a reserved word or illegal names [like with special characters or embedded spaces. The back tick is also like [] square brackets in SQL Server are used in like manner to allow for names that are not normally legal or a reserved word. – John Foll May 29 '20 at 22:51
Your answer seems *slightly* misleading. It wasn't working for me until I reads further down to another answer where someone else had done the same but without the quotation marks. Removing them worked for me. – DiBosco Nov 24 '20 at 19:03
2

Just a quick heads-up - you need to include the entire "column definition", not just `datatype(length)` ([docs for ALTER TABLE](https://dev.mysql.com/doc/refman/5.7/en/alter-table.html)). – rinogo Oct 23 '21 at 19:15
Which form is standard SQL? – vesperto Mar 17 '22 at 10:05
I hate that you have to provide the full column definition to "rename" a column. Wish I could create an alias or something that does this for me because I always forget. – waltmagic Dec 15 '22 at 15:32

Kanke · Answer 2 · 2016-11-29T12:46:37.913

91

In Server version: 5.6.34 MySQL Community Server

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type;

edited Nov 29 '16 at 12:46

answered Nov 29 '16 at 12:38

Kanke

2,527
16
10

92

absolutely dont use DROP and ADD. you will lose all data by doing this – tomazahlin Dec 08 '17 at 10:44
2

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:21
1

Just a quick heads-up - you need to include the entire "column definition", not just `datatype` ([docs for ALTER TABLE](https://dev.mysql.com/doc/refman/5.7/en/alter-table.html)). – rinogo Oct 23 '21 at 19:15
This helped me as the additional 'TO' in other solutions is what was stopping me. Once I removed it, all was well. – TASC Solutions Aug 09 '22 at 15:36

score 49 · Answer 3 · edited Jul 02 '19 at 16:52

49

From MySQL 5.7 Reference Manual.

Syntax :

ALTER TABLE t1 CHANGE a b DATATYPE;

e.g. : for Customer TABLE having COLUMN customer_name, customer_street, customercity.

And we want to change customercity TO customer_city :

alter table customer change customercity customer_city VARCHAR(225);

edited Jul 02 '19 at 16:52

HoldOffHunger

18,769
10
104
133

answered Aug 12 '17 at 18:20

Ashu_FalcoN

916
2
10
21

score 34 · Answer 4 · edited Jun 20 '20 at 09:12

34

From MySQL 8.0 you could use

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

ALTER TABLE Syntax:

RENAME COLUMN:

Can change a column name but not its definition.

More convenient than CHANGE to rename a column without changing its definition.

DBFiddle Demo

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 19 '18 at 15:49

Lukasz Szozda

162,964
23
234
275

score 9 · Answer 5 · answered Apr 13 '21 at 04:18

9

ALTER TABLE `table_name` CHANGE `$old_column_name` `new_column_name` VARCHAR(40)

this is working for me

answered Apr 13 '21 at 04:18

Ravi Ekks

101
1
1

1

Please, add an explanation. See [how to answer](https://stackoverflow.com/help/how-to-answer). – Syscall Apr 13 '21 at 06:46

score 7 · Answer 6 · edited Dec 12 '18 at 03:46

7

You can use following code:

ALTER TABLE `dbName`.`tableName` CHANGE COLUMN `old_columnName` `new_columnName` VARCHAR(45) NULL DEFAULT NULL ;

edited Dec 12 '18 at 03:46

Jase

1,369
2
12
24

answered May 01 '18 at 22:29

sam

87
1
1

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:20

score 4 · Answer 7 · answered Mar 19 '21 at 12:09

Changing name in MySQL we have to use "ALTER" table command followed by "CHANGE". Below is the query.

ALTER TABLE tablename CHANGE COLUMN oldcolname newcolname datatype;

ALTER TABLE tablename CHANGE oldcolname newcolname datatype;

PS- You can add "COLUMN" word or ignore in the query. It will work same.

"RENAME" is used in Oracle database.

score 2 · Answer 8 · answered Sep 22 '18 at 20:05

2

Rename column name in mysql

alter table categories change  type  category_type varchar(255);

answered Sep 22 '18 at 20:05

Dinesh Vaitage

2,983
19
16

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:19

score 2 · Answer 9 · answered Mar 11 '20 at 17:16

2

In mysql your query should be like

ALTER TABLE table_name change column_1 column_2 Data_Type;

you have written the query in Oracle.

answered Mar 11 '20 at 17:16

Ish

384
4
10

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:17

score 2 · Answer 10 · answered Mar 31 '20 at 19:56

2

Syntax: ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

If table name is Student and column name is Name. Then, if you want to change Name to First_Name

ALTER TABLE Student CHANGE Name First_Name varchar(20);

answered Mar 31 '20 at 19:56

mohimenul

29
3

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:18

FARS · Answer 11 · 2022-02-16T23:41:54.463

2

ALTER TABLE table_name CHANGE old_column_name new_column_name columnDataType;

edited Feb 16 '22 at 23:41

answered Feb 11 '22 at 22:58

FARS

313
6
20

score 1 · Answer 12 · answered Nov 27 '19 at 17:44

1

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

For MySQL 8

alter table creditReportXml_temp change column applicationID applicantID int(11);

answered Nov 27 '19 at 17:44

Radagast_Brown

79
1
2

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:19

score 1 · Answer 13 · edited Jan 21 '20 at 13:46

1

for mysql version 5

alter table *table_name* change column *old_column_name* *new_column_name* datatype();

edited Jan 21 '20 at 13:46

Community

1
1

answered Jan 21 '20 at 08:15

unknown

19
1

1

This answer doesn't add much value to the accepted answer. – Noel Yap Mar 19 '21 at 18:19

score 1 · Answer 14 · answered Jan 05 '21 at 21:10

Rename MySQL Column with ALTER TABLE Command

ALTER TABLE is an essential command used to change the structure of a MySQL table. You can use it to add or delete columns, change the type of data within the columns, and even rename entire databases. The function that concerns us the most is how to utilize ALTER TABLE to rename a column.

Clauses give us additional control over the renaming process. The RENAME COLUMN and CHANGE clause both allow for the names of existing columns to be altered. The difference is that the CHANGE clause can also be used to alter the data types of a column. The commands are straightforward, and you may use the clause that fits your requirements best.

How to Use the RENAME COLUMN Clause (MySQL 8.0)

The simplest way to rename a column is to use the ALTER TABLE command with the RENAME COLUMN clause. This clause is available since MySQL version 8.0.

Let’s illustrate its simple syntax. To change a column name, enter the following statement in your MySQL shell:

ALTER TABLE your_table_name RENAME COLUMN original_column_name TO new_column_name;

Exchange the your_table_name, original_column_name, and new_column_name with your table and column names. Keep in mind that you cannot rename a column to a name that already exists in the table.

Note: The word COLUMN is obligatory for the ALTER TABLE RENAME COLUMN command. ALTER TABLE RENAME is the existing syntax to rename the entire table.

The RENAME COLUMN clause can only be used to rename a column. If you need additional functions, such as changing the data definition, or position of a column, you need to use the CHANGE clause instead.

Rename MySQL Column with CHANGE Clause

The CHANGE clause offers important additions to the renaming process. It can be used to rename a column and change the data type of that column with the same command.

Enter the following command in your MySQL client shell to change the name of the column and its definition:

ALTER TABLE your_table_name CHANGE original_column_name new_col_name data_type;

The data_type element is mandatory, even if you want to keep the existing datatype.

Use additional options to further manipulate table columns. The CHANGE also allows you to place the column in a different position in the table by using the optional FIRST | AFTER column_name clause. For example:

ALTER TABLE your_table_name CHANGE original_column_name new_col_name y_data_type AFTER column_x;

You have successfully changed the name of the column, changed the data type to y_data_type, and positioned the column after column_x.

score 1 · Answer 15 · answered May 08 '23 at 17:37

Be careful of most of the answers above. I lost my default value following the accepted answer.

If your column has a default value or other non-default values the correct syntax for MySql 5 is:

ALTER TABLE table_name CHANGE col1 col2 column_definition;

From the Manual:
For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change only INT to BIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;
That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

@rinogo I thought that your comment on a couple of posts should be an answer, since I got burned without it. — Sinc, May 08 '23 at 17:39

Hashim Aziz · Answer 16 · 2023-09-01T17:47:10.573

Remember the backticks

Something that threw me off and that I'm mentioning here explicitly since no other answer seems to - column names need to be surrounded by backticks, of all things. SQL is the only language I've ever come across that insists on backticks for quoting, and the pseudocode of the other answers don't even make it clear that quoting is needed at all.

So for MySQL 8:

ALTER TABLE table_name RENAME COLUMN `old_col_name` TO `new_col_name`;

And for MySQL 7 and earlier:

ALTER TABLE table_name CHANGE `old_col_name` `new_col_name` datatype(length);

score 0 · Answer 17 · answered Feb 16 '22 at 08:28

0

For MySQL <= 8

ALTER TABLE table_names CHANGE `old_column_name` `new_column_name` varchar(50);

answered Feb 16 '22 at 08:28

Netwons

1,170
11
14

How does this answer differ from mine? – FARS Feb 16 '22 at 23:43
I did not get an answer.but I with (`) got the answer – Netwons Feb 17 '22 at 07:33
it's not working for me and is through an error. – Muddasir23 Mar 16 '23 at 07:51

score 0 · Answer 18 · answered Mar 31 '22 at 16:47

Posting it here, it helps helps else ignore it but when trying to use the Change Column and Rename column functions it is throwing me an error. So figured I would see what statement is generated when we go ahead and rename the column by going into table properties. Below is the command been generated.

EXEC DB.sys.sp_rename N'db.tablename.TrackingIDChargeDescription1' , N'ChargeDescription1', 'COLUMN';

I used and renamed bunch of columns in table.

score 0 · Answer 19 · edited Oct 27 '22 at 00:00

0

None of the above worked when I had a column with parenthesis.

Then I tried ` and the magic worked. So if you have a special character in your column by mistake and you want to rename it, use ` for the name of the existing column. For example:

ALTER TABLE table_name RENAME COLUMN column(old) TO new_column;

edited Oct 27 '22 at 00:00

Giacomo1968

25,759
11
71
103

answered Aug 24 '22 at 15:56

Tandra Chakraborty

31
2

score -4 · Answer 20 · answered Sep 22 '21 at 14:35

-4

if you are using gui SQL SMS

you can do db -> Tables -> Table -> columns -> column you want to rename

right click and rename

answered Sep 22 '21 at 14:35

Vedad Herceglija

53
4

Rename a column in MySQL

20 Answers20

Rename MySQL Column with ALTER TABLE Command

How to Use the RENAME COLUMN Clause (MySQL 8.0)

Rename MySQL Column with CHANGE Clause

Remember the backticks

Linked

Related