Mysql union from multiple database tables

Question

I have a group of databases which have a table with same schema. So, i am doing a UNION on this table to show the records from all databases, which works nice.

But, now i have to detect which row belongs to which database/table, as i need to modify that particular record. I found this link on getting the UNION, but am unable to find the logic which determines row<->table relationship.

Answer 1

Not really sure what's allowed in your case, my suggestion is to have an additional field with a number in it:

select field1, field2, 1 as db from db1.tb
UNION
select field1, field2, 2 as db from db2.tb1
UNION
select field1, field2, 3 as db from db1.tb4
UNION

with said field you can identify the source of each record.

Answer 2

try this:

(SELECT *, 'db1' as DB_NAME from db1..table1)
union
(SELECT *, 'db2' as DB_NAME from db2..table1)
union
(SELECT *, 'db3' as DB_NAME from db3..table1)
...