Update if exist, insert row if not exist in MySQL

Question

I have asked similar question to this here...

three tables with Inner Join

But I couldn't get enough answer to solve my problem, so I am forced to re-ask again...

I have the following table...

**

anyone who want to play on the data please test it here http://sqlfiddle.com/#!9/a0807

**

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

the following code will copy or insert from table1 to table to with out problem, but with wrong table2 IDB (because table2 IDB value should come from inner join to table3)

INSERT INTO table2 SELECT * FROM table1 WHERE IDA = 111

But copy all from table1 to table2, which is wrong...

so, what I did is...the following inner join...But not working..

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

That is not working either....

But, what I need is exactly...I want to copy from table1 to table to connected over table3, if the row available update if not insert....

sorry for asking twice, but I am kin to get some idea from you guys...

Please help...thanks in advance...

May be using php, to handle if possible...

Answer 1

Try with ON DUPLICATE KEY like

$sql = "INSERT INTO `table2` (Col2, Col3) 
VALUES ('val1', 'val2')
ON DUPLICATE KEY UPDATE
Col2='val1', Col3='val2'";

EDIT :

INSERT INTO table2 (ColB , ColC)
(SELECT ColB.b,ColC.b FROM table1 b 
    LEFT JOIN table3 c ON c.IDA = b.IDA  
    LEFT JOIN table2 a ON a.IDB = c.IDB
 WHERE IDB = 111
)
ON DUPLICATE KEY UPDATE
ColB = ColB.b   
ColC = ColC.b

And Try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.