sql - How to move columns in a MySQL table?

Question

Currently I am having the following MySQL table: Employees (empID, empName, department);

I want to change the table to the following: Employees (empID, department, empName);

How can this be done using ALTER statements?

Note: I want to change only column positions.

Answer 1

If empName is a VARCHAR(50) column:

ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;

EDIT

Per the comments, you can also do this:

ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR(50) AFTER department;

Note that the repetition of empName is deliberate. You have to tell MySQL that you want to keep the same column name.

You should be aware that both syntax versions are specific to MySQL. They won't work, for example, in PostgreSQL or many other DBMSs.

Another edit: As pointed out by @Luis Rossi in a comment, you need to completely specify the altered column definition just before the AFTER modifier. The above examples just have VARCHAR(50), but if you need other characteristics (such as NOT NULL or a default value) you need to include those as well. Consult the docs on ALTER TABLE for more info.