Can you update multiple db's?

[TapeGun007]

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.

[Psycho]

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

[NigelRel3]

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.

[NigelRel3]

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.