用另一列的数据替换列的数据相同的表名，相同的列名称不同的数据库名称

Question

Updating an older version of prestashop to a newer version. Having trouble creating a query to migrate column data from source db to destination db (same column and table names, different db names).

In other words, I need to replace the data of destination column with data of source column. This is the query I have so far:

INSERT INTO `destination_db`.`destination_table` 
Select * FROM `source_db`.`source_table` 
on duplicate key UPDATE `destination_db`.`destination_table`.`destination_colum`=`source_db`.`source_table`.`source_column`;

After running the query, this is the result: MySQL returned an empty result set (i.e. zero rows)

Any help to fix the query is truly appreciated.

Answer 1

Try this as it seems to me that you may have just made a small mistake with you're syntax

You forgot to put the UPDATE {tableName} SET {column} = {sourceColumn}.

INSERT INTO `destination_db`.`destination_table` (`destination_db`.`destination_table`.'destination_column1',`destination_db`.`destination_table`.'destination_column2',`destination_db`.`destination_table`.'destination_column3')
SELECT 
       `source_db`.`source_table`.'source_column1',
       `source_db`.`source_table`.'source_column2',
       `source_db`.`source_table`.'source_column3'      
FROM 
        `source_db`.`source_table` 
ON 
     DUPLICATE KEY 
            UPDATE `destination_db`.`destination_table`.`destination_column1`=`source_db`.`source_table`.`source_column1`;