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?

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.