三个带有内连接的表 [英] three tables with Inner Join

问题描述

我有以下三个表...

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

我需要的是从 table1 复制到 table2，我可以使用以下简单的 MySQL 查询来做到这一点...

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

问题是我的 id 类型不同，...这两个表通过第三个表 table3 连接.其中IDA包含table1主键，IDB包含table2主键，

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,

因此，例如，如果我想从 table1 IDA(111) 复制到 table2，我该怎么做?如果 IDB 存在，我如何更新重复密钥...

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

推荐答案

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

我现在没有 MySQL，所以语法可能不是 100% 正确，但这应该让你知道它应该如何完成.根据 table3 是否有每个 table1 id 的条目，您可能需要将 t3.idb 更改为 coalesce(t3.idb, t1.ida) 并将查询中的 join 更改为 left join 如果您希望它们被复制.请记住，table2 将有来自 table1)

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)

这篇关于三个带有内连接的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！