Manipulating MySQL column and table data

Often times I find it necessary to restructure data in my MySQL database for various reasons.  Usually it is because I have a better data structure in mind to give a site more functionality.  In order to perform the changes I need to either move data from one column to another or move the data from certain columns in a table to another table.   Moving data between tables is what I find more useful because a lot of the time copying data from one column to another (within the same table) is really just the same as renaming a column.

Here are a few things that I find very useful to know.

Copy data from one column to another:

Say you have a table (myTable) with two fields (field_1 and field_2) and you want to move the data from field_2 to field_1.

UPDATE myTable SET field_1 = field_2

But if you only want to perform the operation if a certain condition exists then you’ll need to add a where clause.

UPDATE myTable SET field_1 = field_2 WHERE field_2 == condition

Copy data from one table to another:

Now, say you have two tables (table_1 and table_2) and you want to copy the data from a field in table_1 to a different field in table_2

INSERT INTO table_2 ( table_2_field ) SELECT table_1_field FROM table_1

Again, if you want to perform the operation when a certain condition exists then you just need to add a where clause.

INSERT INTO table_2 ( table_2_field ) SELECT table_1_field FROM table_1 WHERE table_1_field == condition

Rename a column

If you need to rename a column there are multiple ways of doing it, but the following is the simplest:

ALTER TABLE myTable RENAME myNewTable

Move a column

If you need to move a column so it’s the first column in the table then use the following:

ALTER TABLE myTable MODIFY field_2 column_definition FIRST

Where column_definition is the type of column (ex. varchar(255), int(2), etc…)

If you need to move a column so it’s place after a given column use the following:

ALTER TABLE myTable MODIFY field_1 column_definition AFTER field_2