Copy one column from one database to another

Question

I need to copy content from a column in one database to the corresponding column in another so that the same content gets into a record with the same identifier. Something like the following pseudo file:

SET database2.table1.columnA TO database1.table1.columnA WHERE database2.id = database1.id 

Answer 1

You can use JOIN in an UPDATE statement :

 UPDATE table1 t1 JOIN database1.table1 as t2 ON t1.id = t2.id SET t1.columnA = t2.columnA 

Answer 2

MySQL uses the syntax:

 update database1.table1, database2.table1 set database1.table1.columnA = database2.table1.columnA where database1.table1.id = database2.table1.id; 

Answer 3

If the columns are not identical for other people, you can use below:

 USE `old_database`; INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`) SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` FROM `old_table`