Reputation: 11267
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.
Upvotes: 262
Views: 248376
Reputation: 2512
I had to run this for a column introduced in the later stages of a product, on 10+ tables. So wrote this quick untidy script to generate the alter command for all 'relevant' tables.
This will generate an ALTER TABLE command for all TABLES in the schema that contain the COLUMN that you're trying to move to a different position.
SET @NeighboringColumn = '<YOUR COLUMN SHOULD COME AFTER THIS COLUMN>';
SELECT CONCAT("ALTER TABLE `",t.TABLE_NAME,"` CHANGE COLUMN `",COLUMN_NAME,"`
`",COLUMN_NAME,"` ", c.DATA_TYPE, CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT
NULL THEN CONCAT("(", c.CHARACTER_MAXIMUM_LENGTH, ")") ELSE "" END ," AFTER
`",@NeighboringColumn,"`;")
FROM information_schema.COLUMNS c, information_schema.TABLES t
WHERE c.TABLE_SCHEMA = '<YOUR SCHEMA NAME>'
AND c.COLUMN_NAME = '<COLUMN TO MOVE>'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'
AND @NeighboringColumn IN (SELECT COLUMN_NAME
FROM information_schema.COLUMNS c2
WHERE c2.TABLE_NAME = t.TABLE_NAME);
Upvotes: 2
Reputation: 111
In SQL :
If you want to move id
column to the first place, we have a query for that
, is like below:
ALTER TABLE `mydatabase` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT FIRST;
In this query, information is like below:
But if you want to move a column after another column
, mean maybe your A column is at the secound and you want to move it to the last place of your table after B column so use this query:
ALTER TABLE `mydatabase` CHANGE `title` `title` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `img_name`;
The information of this query is like below:
In PHPMYADMIN :
+
inside of your table, click on COLUMNS
.change
under
column action
(column you want to move). Now you see another page,Move column
. It is select option and choose place
you want to move that column.save
button.Upvotes: 1
Reputation: 8007
For those using TablePlus, you can just mark all tables, right click -> Copy, in the new table -> Paste.
Upvotes: 0
Reputation: 234857
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.
Upvotes: 426
Reputation:
phpMyAdmin provides a GUI for this within the structure view of a table. Check to select the column you want to move and click the change action at the bottom of the column list. You can then change all of the column properties and you'll find the 'move column' function at the far right of the screen.
Of course this is all just building the queries in the perfectly good top answer but GUI fans might appreciate the alternative.
my phpMyAdmin version is 4.1.7
Upvotes: 21
Reputation: 851
Change column position:
ALTER TABLE Employees
CHANGE empName empName VARCHAR(50) NOT NULL AFTER department;
If you need to move it to the first position you have to use term FIRST at the end of ALTER TABLE CHANGE [COLUMN] query:
ALTER TABLE UserOrder
CHANGE order_id order_id INT(11) NOT NULL FIRST;
Upvotes: 85