Rename column name of a table in MySQL

Question

ERROR 1064 (42000): 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 'sp_rename 'records.student_id', 'id', 'COLUMN'' at line 1.

Help to resolve this error.

mysql> sp_rename 'records.student_id', 'id', 'COLUMN';

this is sql-server syntax. not mysql. Read about [alter table](http://dev.mysql.com/doc/refman/5.7/en/alter-table.html) — Jens, Nov 25 '16 at 13:07
@Jens:does the above code is wrong??...i came across this code in the internet , to rename the column. — deka4tech, Nov 25 '16 at 13:17
@deka4tech It is wrong for mysql, because it is for SQL-Server from microsoft! — Jens, Nov 25 '16 at 13:18
@Jens: ALTER TABLE is not working..the one u prescribed..throwing following error-mysql> ALTER TABLE records CHANGE 'student_id' 'id' INT Not NULL; ERROR 1064 (42000): 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 ''student_id' 'id' INT Not NULL' at line 1 – — deka4tech, Nov 25 '16 at 13:24
@deka4tech remove the single quotes arround column names: ALTER TABLE records CHANGE student_id id INT Not NULL — Jens, Nov 25 '16 at 13:25
@Jens:mysql> ALTER TABLE records CHANGE student_id id INT NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-49c_34' to './test/records' (errno: 150) — deka4tech, Nov 25 '16 at 13:28
@deka4tech Looks like you have an index on that table drop it before and recreate it after renaming — Jens, Nov 25 '16 at 13:29
@Jens: The student_id column is defined as primary key...can it be a reason for the error??? — deka4tech, Nov 25 '16 at 13:32
@Jens: what u said about the index thing on the table(dropping it)..I didnt understand...what is that?? — deka4tech, Nov 25 '16 at 13:35
@deka4tech You should learn about the basics before using a db — Jens, Nov 25 '16 at 13:37
@Jens: can u provide some link to learn that index stuff..it would be great help — deka4tech, Nov 25 '16 at 13:40
@deka4tech Also read http://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me — Jens, Nov 25 '16 at 13:43

ttwis · Answer 1 · 2016-11-25T13:42:43.940

3

Try like this. Without quotes.

ALTER TABLE records CHANGE student_id id INT(6) NOT NULL AUTO_INCREMENT;

edited Nov 25 '16 at 13:42

answered Nov 25 '16 at 13:33

ttwis

368
1
4
16

student_id is declared as primary key...can it causing the problem..has something more to be added in the query after the NOT NULL in the end???? – deka4tech Nov 25 '16 at 13:38
1

You should remove the blanks inside the backticks – Jens Nov 25 '16 at 13:42
mysql> ALTER TABLE `records` CHANGE `student_id` `id` INT(6) NOT NULL AUTO_INCREMENT; ERROR 1025 (HY000): Error on rename of './test/#sql-49c_34' to './test/records' (errno: 150) – deka4tech Nov 25 '16 at 13:44

jophab · Answer 2 · 2016-11-25T13:25:58.953

1

sp_rename is for SQL Server. Use ALTER TABLE for MYSQL

 ALTER TABLE `records` CHANGE `student_id` `id` INT NOT NULL;

For SQL Server

EXEC sp_rename 'records.student_id', 'id', 'COLUMN'

edited Nov 25 '16 at 13:25

answered Nov 25 '16 at 13:16

jophab

5,356
14
41
60

:its not working..throwing following error-mysql> ALTER TABLE records CHANGE 'student_id' 'id' INT Not NULL; ERROR 1064 (42000): 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 ''student_id' 'id' INT Not NULL' at line 1 – deka4tech Nov 25 '16 at 13:20
@deka4tech I think u used quote instead of backticks around column names – jophab Nov 25 '16 at 13:26
mysql> ALTER TABLE `records` CHANGE `student_id` `id` `COLUMN` INT NOT NULL; ERROR 1064 (42000): 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 '`COLUMN` INT NOT NULL' at line 1 – deka4tech Nov 25 '16 at 13:31
student_id is defined as primary key..can it be a reason for the error?? – deka4tech Nov 25 '16 at 13:33
You arent executing the same query. Where did COLUMN came from in ALTER TABLE? – jophab Nov 25 '16 at 13:37
mysql> ALTER TABLE records CHANGE student_id id int NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-49c_34' to './test/records' (errno: 150) – deka4tech Nov 25 '16 at 13:40
@deka4tech the link jens provided will help – jophab Nov 25 '16 at 13:44
@deka4tech Is it have something to do with foreign keys – jophab Nov 25 '16 at 13:47

Rename column name of a table in MySQL

2 Answers2