如果表C中Col A和Col B的组合不存在，则在表3中插入新行 [英] Insert New Row in Table 3 if combination of Col A and Col B in Table C Don't Exist

问题描述

我有一个从表1和表2获取数据的代码，然后在表3中插入新行。我的问题是代码添加了已经存在的记录。当表C中的 groupid 和 userid 的组合已经存在时，如何防止插入重复错误？

  INSERT INTO mdl_groups_members（groupid，userid）
 SELECT l.mgroup AS moodle，r.id AS mdl_user 
 FROM moodle AS l 
 JOIN mdl_user AS r 
 ON l.orders_id = r.id 
 WHERE l.mgroup> 0 
  

以下是我运行脚本之前的表：

  id groupid userid timeadded 
 1 1 1 1372631339 
 2 4 2 1372689032 
 3 8 3 1373514395 
 4 3 4 1373514395 
  

这是运行脚本后的表。我在我不想插入的重复行旁边放置一个*。

  id groupid userid timeadded 
 1 1 1 1372631339 
 2 4 2 1372689032 
 3 8 3 1373514395 
 4 3 4 1373514395 
 * 5 1 1 1372631339 
 * 6 4 2 1372689032 
 * 7 8 3 1373514395 
 * 8 3 4 1373514395 
 9 2 6 1373514398 
  

我已经尝试过这个网站上的许多解决方案，但没有一个工作。我欢迎所有的建议。

谢谢，
Matt

解决方案

p>如果要避免重复，请添加唯一索引或约束：

 在mdl_groups_members上创建唯一索引idx_mdl_groups_members_2（groupid， userid）
  

然后在重复键更新时使用 ：

  INSERT INTO mdl_groups_members（groupid，userid）
 SELECT l.mgroup AS moodle，r.id AS mdl_user 
 FROM moodle l JOIN 
 mdl_user r 
 ON l.orders_id = r.id 
 WHERE l.mgroup> 0 
 ON DUPLICATE KEY UPDATE groupid = VALUES（groupid）; 
  

重复密钥更新上的导致 insert 忽略重复错误，插入其他行，没有问题。

I have a code that gets data from Tables 1 and 2, and then inserts new rows into Table 3. My problem is that the code adds records that already exist. How can I prevent duplicate errors from being inserted, when the combination of groupid and userid in Table C already exists?

INSERT INTO mdl_groups_members (groupid,userid)
SELECT l.mgroup AS moodle, r.id AS mdl_user  
    FROM moodle AS l 
    JOIN mdl_user AS r   
        ON l.orders_id = r.id
WHERE l.mgroup > 0

Here's the table before I ran the script:

id  groupid userid  timeadded
1   1       1       1372631339
2   4       2       1372689032
3   8       3       1373514395
4   3       4       1373514395

Here's the table after I ran the script. I placed a "*" next to the duplicated rows that I don't want to insert.

id  groupid userid  timeadded
1   1       1       1372631339
2   4       2       1372689032
3   8       3       1373514395
4   3       4       1373514395
*5  1       1       1372631339
*6  4       2       1372689032
*7  8       3       1373514395
*8  3       4       1373514395
9   2       6       1373514398

I've tried many of the solutions on this site, but none have worked. I welcome all advice.

Thanks, Matt

解决方案

If you want to avoid duplicates, then add a unique index or constraint:

create unique index idx_mdl_groups_members_2 on mdl_groups_members(groupid, userid)

Then use on duplicate key update:

INSERT INTO mdl_groups_members (groupid, userid)
    SELECT l.mgroup AS moodle, r.id AS mdl_user  
    FROM moodle l JOIN
         mdl_user r   
         ON l.orders_id = r.id
    WHERE l.mgroup > 0
    ON DUPLICATE KEY UPDATE groupid = VALUES(groupid);

The on duplicate key update causes the insert to ignore duplication errors, inserting other rows with no problems.

这篇关于如果表C中Col A和Col B的组合不存在，则在表3中插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！