Jump to content
TapeGun007

Can you update multiple db's?

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.db1tablename
INNER JOIN db2name.db2tablename
  ON db1name.db1tablename.fieldnameA = db2name.db2tablename.fieldnameX

SET db1name.db1tablename.fieldnameB = db2name.db2tablename.fieldnameY

Edited by Psycho

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.