TapeGun007 Posted April 12, 2017 Share Posted April 12, 2017 I know you can query multiple DB's using UNION, but can you also UPDATE multiple db's, is that even possible? Not something I really want to do, but I'm trying to work around what I inherited until we hire internal dev's to fix this. Quote Link to comment https://forums.phpfreaks.com/topic/303693-can-you-update-multiple-dbs/ Share on other sites More sharing options...
Psycho Posted April 12, 2017 Share Posted April 12, 2017 (edited) In a single query? I've only updated one table with a query - unless you also consider triggers or an update clause that uses a JOIN to another table for the purposes of filtering or determining values. I don't know if you could have triggers work across databases, but a JOIN condition could potentially work as long as you include a full reference to the tables: databasename.tablename.fieldname. Have you tried it? What exactly are you trying to accomplish? ---------------------------------- EDIT: . . . a JOIN condition could potentially work as long as you include a full reference to the tables Out of curiosity, I tested this and it is possible. E.g. UPDATE db1name.db1tablenameINNER JOIN db2name.db2tablename ON db1name.db1tablename.fieldnameA = db2name.db2tablename.fieldnameX SET db1name.db1tablename.fieldnameB = db2name.db2tablename.fieldnameY Edited April 12, 2017 by Psycho Quote Link to comment https://forums.phpfreaks.com/topic/303693-can-you-update-multiple-dbs/#findComment-1545375 Share on other sites More sharing options...
NigelRel3 Posted April 13, 2017 Share Posted April 13, 2017 In MySQL you can update several tables in the same statement... Update table1, table2 set table1.qty = table1.qty+1, table2.qty = table2.qty-1 where table1.id = table2.id If you also consider that a table could use the federated storage engine, this lets you have a table on remote databases, so each table in an update could be on a different database. There are all sorts of caveats with this sort of table (e.g. transactions are not supported) but if this is the best solution, then it may be worth trying. Quote Link to comment https://forums.phpfreaks.com/topic/303693-can-you-update-multiple-dbs/#findComment-1545405 Share on other sites More sharing options...
NigelRel3 Posted April 13, 2017 Share Posted April 13, 2017 I don't know if you could have triggers work across databases, but a JOIN condition could potentially work as long as you include a full reference to the tables: databasename.tablename.fieldname. Have you tried it? One thing with this is as long as the databases sit on the same server, it's easier than using federated tables, but again, depends on your circumstances. Quote Link to comment https://forums.phpfreaks.com/topic/303693-can-you-update-multiple-dbs/#findComment-1545406 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.