4

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.

radbyx
  • 9,352
  • 21
  • 84
  • 127
web-nomad
  • 6,003
  • 3
  • 34
  • 49

5 Answers5

7

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.

radbyx
  • 9,352
  • 21
  • 84
  • 127
kasavbere
  • 5,873
  • 14
  • 49
  • 72
  • I am typing on my cellphone. By the time I finished, another poster beat ne to it. U am glad you got the corrwct answer you sought. – kasavbere Apr 10 '12 at 13:42
3

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)
...
Andrey Gurinov
  • 2,825
  • 1
  • 20
  • 23
1

You could just add a column with database name in the queries

(SELECT *,'db1' as db from db1..us_music where `genre` = 'punk')
UNION
(SELECT *,'db2' as db from db2..us_music where `genre` = 'punk')
Vivek Viswanathan
  • 1,968
  • 18
  • 26
1

Let's say you have a completely separate database, Oracle, SQL Server or the data resides on a separate server where they can't be UNIONed directly (this happens to us with an (PeopleSoft/Oracle) ERP database and an engineering DB (MySQL) for test results). You can do the query on one of the DBs, keep the result set in memory and construct a query like this:

SELECT '' AS SN, 'PRD0547016' AS Prdn_ID UNION
SELECT '' AS SN, 'PRD0547435' AS Prdn_ID UNION
SELECT sn, pid AS Prdn_ID
    FROM build left join product on prod_idx=product.idx WHERE sn='23456-1' 
       AND PID not in ('dgh')
        ORDER BY Prdn_ID;

In this particular case, I end up with:

'23456-1', 'pid 2'
'23456-1', 'pid 3'
'', 'PRD0547016'
'', 'PRD0547435'

I had wanted to SELECT INTO, yada, yada..., but transferring files from server to server, creating temporary tables, etcetera; was too much programming and I'll bet this is the computationally fastest solution too.

Danny Holstein
  • 144
  • 1
  • 14
0

Try DATABASE() function

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_database

uisky
  • 125
  • 1
  • 1
  • 7
  • it will only tell me which database am i currently connected to. But, the result will get results from multiple databases. I am looking to find to which database / table does the current result belong. – web-nomad Apr 10 '12 at 13:29