Updating Tables across multiple Databases

Recently I was asked if it is possible to update tables with data from other databases. Of course it is 🙂

Imagine the case where you want to update your customer table with data from a backup database. Let’s call the current database currentDB and the backup backupDB and the customer table respectively customer. Furthermore, let’s say column address got messed up and you want to restore it from the backup database. The following SQL statement does this job:

UPDATE  `currentDB`.`customer` `cdb`
JOIN `backupDB`.`customer` `bdb`
ON `cdb`.`customer` = `bdb`.`customer`
SET `cdb`.`address` = `bdb`.`address`;

As you can see updating tables across multiple databases straight forward. You only need to join the tables from your databases (based on a private key as usual) and set the values accordingly.

Pretty easy, right?