How to rename a mysql table and column in a single query?

October 1, 2023

I’m writing a mysql workbench. I’d like to update a table and columns with a single query. Would I need to use a nested query or is what I’m trying to do not possible?

ALTER TABLE MyTable CHANGE id id2 int; // works

ALTER TABLE MyTable TO|RENAME MyTable2 CHANGE id id2 int; // fails

>Solution :

Yes. You can make multiple changes in one ALTER TABLE statement.

The syntax reference shows:

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

Note the optional additional alter_option separated by comma.

Demo:

mysql> create table mytable (id serial primary key, x int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table mytable rename column x to y, rename as myothertable;
Query OK, 0 rows affected (0.01 sec)