three tables with Inner Join

Question

I have the following three tables...

Table1
IDA    colB    colC    
111       a       w    
222       b       w    
333       c       s        
444       b       g    



Table2
IDB    colB    colC    
11       w       f    
12      w       r    
13      s       g    



Table3
IDA     IDB       
111     11         
222     12           
333     13       
444     14

What I need is to copy from table1 to table2 and I could use the following easy MySQL query to do that...

INSERT INTO table2 SELECT * FROM table1

The problem is I don't the same id type,...the two tables are connected over the third table table3. in which IDA contains table1 primary key and IDB contain table2 primary key,

so, example if I want to copy from table1 IDA(111) to table2 how do I do that? and if the IDB exists how do I update on Duplicate Key...

I have the following query but no working...

INSERT INTO table2 SELECT * FROM table1 
WHERE IDA IN ( SELECT table1 b 
INNER JOIN table3 c ON c.IDA = b.IDA 
INNER JOIN table2 a ON a.IDB = c.IDB ) 
WHERE b.IDA=111

But, I wish if I get generalize answer...Thanks

Answer 1

INSERT INTO table2 
SELECT 
    t3.idb
    ,t1.colb as ncolb
    ,t1.colc as ncolc
FROM 
    table1 t1
    join table3 t3
    on t1.ida = t3.ida
ON DUPLICATE KEY UPDATE
    colb = ncolb
    ,colc = ncolc

No MySQL on me right now so syntax might not be 100% correct, but this should give you the idea of how it should be done. Depending on whether table3 has entry for each table1 id you might need to change t3.idb to coalesce(t3.idb, t1.ida) and change join to left join in the query if you want them to be copied. Remember that table2 will then have ids from table1)

Answer 2

INSERT INTO table2 SELECT * FROM table1 
WHERE IDA IN ( SELECT * FROM table1 b 
INNER JOIN table3 c ON c.IDA = b.IDA 
INNER JOIN table2 a ON a.IDB = c.IDB ) HAVING b.IDA=111