如果存在则进行更新，如果MySQL中不存在则插入行

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

任何想玩数据的人请在这里测试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)

下面的代码将从table1复制或插入到表格中，以解决问题，但是使用错误的table2 IDB(因为表2 IDB值应该从内连接到表3)

INSERT INTO table2 SELECT * FROM table1 WHERE IDA = 111

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

但是从表1复制到表2，这是错误的……

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

但是，我真正需要的是……我想复制从表1到表在table3连接,如果行可用更新如果不是插入....

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

可能使用php，如果可能的话…

2 个解决方案

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

由于整个ext/mysql PHP扩展提供了所有以前缀mysql_*命名的函数，所以尽量避免使用mysql_*语句，因为PHP v5.5.0将会被正式弃用，将来也会被删除。

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.

还有另外两个MySQL扩展可以更好地使用:MySQLi和PDO_MySQL，这两个扩展都可以替代ext/ MySQL。

#2

use query like this

使用这样的查询

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

插入到表2 (ColB,ColC)从表1中选择ColB,ColC，其中IDA = '111'

#1