Using MySQL, I want to do all these in one insert:
- table T1 contains column Ca & Cb. T1 is in database D1. Insert into table T1 specifying column Ca, Cb.
- table T2 contains column C2. T2 is in database D2. Set T1.Ca's value with T2.C2
- table T3 contains column C3 & C4. T3 is in database D2. use the T2.C2 value to query T3.C3 and use the C4 value of the same row to set T1.Cb
insert into T1(Ca, Cb)
If i understand correcly you should use a join and not an union
insert into D1.T1(Ca, Cb) select T2.C2, T3.C4 from D2.T2 INNER JOIN D2.T3 on T2.C2 = T3.C3
the second select of the union don't know the content of the firts select .. so you have the error
Unknown column 'T2.C2' in 'where clause'