mysqlrenamealter

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

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)