MySQL: Moving columns from one table to another

Here are the steps you can use to move a column from one table to another (within the same database) using MySQL commands.


1) Create a new column in the table you are moving your column to. You will want it to make sure the new column is created to hold the same type of data as the column you are moving. Here is a code example:

alter table destinationtable add column destinationcolumn varchar (250) ; 

2) Now, we will copy the contents of the existing column to the new column we just created:

INSERT INTO `destinationtable`( destinationcolumn )
SELECT existingcolumn
FROM `existingtable`

3) You can also add a condition to the statement if you are moving multiple columns and need to make sure that the data is placed correctly:

UPDATE `destinationtable`,`existingtable` SET destinationtable.destinationcolumn=existingtable.existingcolumn
WHERE destinationtable.productid=existingtable.productid



Meet the author

Libby Fisher is an experienced freelance web developer, recently relocated from Seattle to Boston, and passionate about developing websites that are both aesthetically appealing and intuitively usable - or as she prefers: "beautifully effective."