443

How do I rename a column in table xyz? The columns are:

Manufacurerid, name, status, AI, PK, int

I want to rename to manufacturerid

I tried using PHPMyAdmin panel, but I get this error:

MySQL said: Documentation
#1025 - Error on rename of '.\shopping\#sql-c98_26' to '.\shopping\tblmanufacturer' (errno: 150)
Improve this question
2

9 Answers 9

Reset to default
753

Lone Ranger is very close... in fact, you also need to specify the datatype of the renamed column. For example:

ALTER TABLE `xyz` CHANGE `manufacurerid` `manufacturerid` INT;

Remember :

  • Replace INT with whatever your column data type is (REQUIRED)
  • Tilde/ Backtick (`) is optional
Improve this answer
6
  • 12
    I think the column contains a foreign key to getting the error of the answer so you have to Drop foreign key, alter table, and add foreign key (it's better to backup first) and u can alter it by only changing the name in right click on the table - alter table
    – Chris Sim
    Commented May 9, 2014 at 10:06
  • 7
    Keep in mind that in this solution you lose all other column definitions such as nullability, default value, etc. (see: stackoverflow.com/questions/8553130/…).
    – Dejan
    Commented Jul 13, 2015 at 10:50
  • So basically, instead of MODIFY column <TYPE> (for redefining a column), it's CHANGE column new_column <TYPE>?
    – mwfearnley
    Commented Oct 13, 2015 at 15:54
  • SHOW CREATE TABLE table_name can help you figure out current column definition
    – Mr. Deathless
    Commented Jan 21, 2016 at 18:32
  • @Dejan thanks and this is really annoying, why require a type when you can't specify any other modifiers? Either require a type + modifiers or don't require it at all.
    – JMac
    Commented Apr 12, 2016 at 5:12
44

The standard MySQL rename statement is:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name 
CHANGE [COLUMN] old_col_name new_col_name column_definition 
[FIRST|AFTER col_name]

For this example:

ALTER TABLE xyz CHANGE manufacurerid manufacturerid datatype(length)

Reference: MYSQL 5.1 ALTER TABLE Syntax

Improve this answer
42

FOR MYSQL:

ALTER TABLE `table_name` CHANGE `old_name` `new_name` VARCHAR(255) NOT NULL;

FOR ORACLE:

ALTER TABLE `table_name` RENAME COLUMN `old_name` TO `new_name`;
Improve this answer
1
  • 3
    It doesn't work if you use " for the column names for MySQL. Use nothing or ` instead.
    – Alexis N-o
    Commented Jul 6, 2015 at 17:37
14

EDIT

You can rename fields using:

ALTER TABLE xyz CHANGE manufacurerid manufacturerid INT

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Improve this answer
3
  • 1
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    – Bharanikumar
    Commented Oct 23, 2010 at 4:02
  • 17
    This would be useful, but it's not true to the documentation you cited: "When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column [...] Attributes present in the original definition but not specified for the new definition are not carried forward. "
    – artfulrobot
    Commented Jan 19, 2013 at 23:34
  • it has to be "ALTER TABLE xyz CHANGE manufacurerid manufacturerid datatype(length)"
    – Chris Sim
    Commented May 9, 2014 at 8:30
7

There is a syntax problem, because the right syntax to alter command is ALTER TABLE tablename CHANGE OldColumnName NewColunmName DATATYPE;

Improve this answer
2

With MySQL 5.x you can use:

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name DATATYPE NULL DEFAULT NULL;
Improve this answer
1

Renaming a column in MySQL :

ALTER TABLE mytable CHANGE current_column_name new_column_name DATATYPE;
Improve this answer
0
0

ALTER TABLE CHANGE ;

Example:

ALTER TABLE global_user CHANGE deviceToken deviceId VARCHAR(255) ;
Improve this answer
-5

SYNTAX

alter table table_name rename column old column name to new column name;

Example:

alter table library rename column cost to price;

Improve this answer
4
  • 2
    In mysql RENAME is used for renaming table not a column ,to rename a column use CHANGE.
    – jathin
    Commented May 27, 2015 at 8:14
  • Question is for MySQL - this isn't valid syntax in MySQL.
    – tjbp
    Commented Apr 14, 2016 at 16:33
  • As others note, please use valid MySQL syntax.
    – fool4jesus
    Commented Mar 22, 2017 at 18:55
  • There is invalid mysql syntax.
    – Hasib Kamal Chowdhury
    Commented Apr 1, 2018 at 8:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.