mysql - 如何通过连接其他表插入到一个表中？

Question

我有两个表:

table1
id | mcc | mnc | active | client_id
1    202   05      1        4
2    202   06      0        4 
.......a lot

table2
id | mcc | mnc | rejectReason
1    202   05         null
2    202   06         null

需要插入到 table2 中，但需要检查 table1 的每个项目以插入 table2，如下所示:

 SELECT table1 t1

(CASE WHEN t2.id != 0
THEN
  INSERT INTO table2 t22
  SET (
       t22.rejectReason = CONCAT('LOSS OF COVERAGE'),
       t22.mcc = t1.mcc,
       t22.mnc = t1.mnc,
  )
 WHERE t22.mcc     = t1.mcc
 AND   t22.mnc     = t1.mnc
 ELSE
   ''
END)

 LEFT JOIN table2 t2
 ON  t2.mcc       = t1.mcc
 AND t2.mnc       = t1.mnc

如果 table1 没有像 table2 这样的项目(mcc/mnc)，则为 table2 插入当前项目。请帮忙

RESULT:

 table1
    id | mcc | mnc | active | client_id
    1    202   05      1        4
    2    202   06      0        4 
    3    214    0      1        5
    .....
         212    16     // not exist 
         214    07     // not exist 
    .......a lot
    
    table2
    id | mcc | mnc | rejectReason
    1    202   05         null
    2    202   06         null
    3    212   16         LOSS OF COVERAGE // then insert 
    4    214   07         LOSS OF COVERAGE // then insert 

Answer 1

嗯，我不太明白你想要什么，但这里有一个结构应该如何，根据你的意思调整它:

UPDATE table2 t2
LEFT JOIN table1 t1
 ON(t1.mcc = t2.mcc and t1.mnc = t2.mnc)
SET t2.rejectReason = 'LOSS OF COVERAGE'
WHERE t1.id is null

当表 1 上没有具有相同 mcc,mnc 的记录时，这会将 t2.rejectReason 更新为“LOSS OF COVERAGE”。顺便说一句，CONCAT() 用于合并超过 1 个字符串，您也有一个，因此您不需要它。

编辑:如果您想要插入，则:

INSERT INTO table2
SELECT t.id,t.mcc,t.mnc,'LOSS OF COVERAGE'
FROM Table1 t
WHERE NOT EXISTS(SELECT 1 from table2 s
                 WHERE t.mcc = s.mcc and t.mnc = s.mnc)

EDIT2:我不知道这个错误，所以也许它可以更容易地解决，但你可以这样做:

CREATE TABLE Temp_Tbl AS
(SELECT t.id,t.mcc,t.mnc,'LOSS OF COVERAGE'
FROM Table1 t
WHERE NOT EXISTS(SELECT 1 from table2 s
                 WHERE t.mcc = s.mcc and t.mnc = s.mnc));

INSERT INTO Table2
SELECT * FROM Temp_Tbl;

DROP TABLE Temp_Tbl;