4

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

Community
  • 1
  • 1
MR.Internet
  • 547
  • 4
  • 19

2 Answers2

7

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.

GautamD31
  • 28,552
  • 10
  • 64
  • 85
  • Why do you innerjoin..??While inserting – GautamD31 Aug 23 '13 at 09:41
  • could you please write the query according to the above table, As example Please???? – MR.Internet Aug 23 '13 at 09:42
  • did you see the question well,??? I have two tables but their id comes from the third table, – MR.Internet Aug 23 '13 at 09:43
  • how do you copy from one table to other with out a common ID?? the ID is in the third table....Please check the question again...I will be very delighted if you come up with some sort of query......from the above tables, Please???? Thanks – MR.Internet Aug 23 '13 at 09:44
  • please check it here...your query seems has problem...please check it live...http://sqlfiddle.com/#!9/a0807 – MR.Internet Aug 23 '13 at 10:15
  • @Gautam3164 Isn't that multiple fields update require `,` sign after `ON DUPLICATE KEY UPDATE`? – checksum Jan 09 '14 at 01:17
  • This certainly is working, but the `AUTO_INCREMENT` value will increase, even if nothing is added. So doing this long enough might cause the `AUTO_INCREMENT` value get out of the default `INT(11)`. See this answer for a solution which leaves the `AUTO_INCREMENT` value as it is. http://stackoverflow.com/questions/9189102/too-many-auto-increments-with-on-duplicate-key-update – Timo002 Jun 02 '14 at 21:04
0

use query like this

INSERT INTO Table2 (ColB,ColC) SELECT ColB,ColC FROM Table1 WHERE IDA = '111'

Rejayi CS
  • 1,034
  • 1
  • 10
  • 22