Move a table between MySQL databases

Having organized a lot of datasets in severeal MySQL databases on the same (local)-server the I needed to move a table to another database. The posted solution is copy-paste from Eric Bergen on the MySQL forum:

Alter table can be used to move tables from one db to another.

alter table rename


Modify MySQL Tables

After setting up my first small databases the next learning steps concern basic operation on existing tables in a MySQL database, such as deleting or creating additional variables/columns in a table. I found very helpful:

Delete a variable/column

To delete the column MyCol from the table MyTable, use the following SQL command:


Comment: used single-quotes around MyCol and MyTable which I found not necessary.

Add a column/variable

To add a column called email […] with a datatype of VARCHAR(60) to the table contacts, use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:


Update/Change the values in a column

One would use the UPDATE statement so I looked it up in the reference

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

In order to update the column intensity in the table measures from “20%” to simply “20” one would
UPDATE measures SET intensity="20" WHERE intensity="20%";