brewphone brewphone - 1 year ago 53
SQL Question

SQL query from multiple table with condition

Using MySQL, I want to do all these in one insert:

  1. table T1 contains column Ca & Cb. T1 is in database D1. Insert into table T1 specifying column Ca, Cb.

  2. table T2 contains column C2. T2 is in database D2. Set T1.Ca's value with T2.C2

  3. 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

so I did:

insert into T1(Ca, Cb)
select C2
from D2.T2
select C4
from D2.T3
where C3=T2.C2;

Error Code: 1054. Unknown column 'T2.C2' in 'where clause'

Please help. Thank you in advance.

Answer Source

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'