1

I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2).

The problem is, that 'name' variable in second db is located in another table (table2). It is possible to do this without changing DB structure using 'id' variable from db2 to synchronizing?

UPDATE `db1`.`table1`, `db2`.`table2` SET `db2`.`table1`.`birthday` = `db1`.`table1`.`birthday` WHERE `db2`.`table1`.`name` ... 

Scheme:

 db1-> table1 -> name,birthday
 db2-> table1 -> name,id     
       table2 -> birthday,id
  • You can join the 3 tables. It doesn't matter if they are in the same database or not. – ypercubeᵀᴹ Apr 1 '14 at 19:11
1

If your db2.table1 and db2.table2 tables are joined by some key field and the name field in both databases is unique, you can use the following query to update your data:

UPDATE `db1`.`table1` t SET 
t.`birthday` = 
(SELECT t2.`birthday` FROM `db2`.`table1` t1 JOIN
`db2`.`table2` t2 ON (t1.`key` = t2.`key`)
WHERE t.`name` = t1.`name`)

This should work if you need to update db2 -> db1. If you need to update db1 -> db2, the query can be changed as follows:

UPDATE `db2`.`table2` t2 SET t2.`birthday` = 
(SELECT t.`birthday` FROM `db1`.`table1` t  WHERE t.`name` = 
(SELECT t1.`name` FROM `db2`.`table1` t1 WHERE t1.`key` = t2.`key`))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.