SQL：如何在没有重复项的情况下添加到表中？

So I am currently trying to take one table and add it into another table but for some reason it is not working the way I want it to. There are three columns in both the tables and I only want to add each row of data from table 2 to table 1 if the first 2 columns of table 2 are not already in table 1 (I dont care about the 3rd column)

所以我目前正在尝试将一个表添加到另一个表中但由于某种原因它不能按照我想要的方式工作。这两个表中有三列，如果表2的前两列不在表1中，我只想将表2中的每一行数据添加到表1中（我不关心第3列）

This is what I have so far:

这是我到目前为止：

INSERT INTO table1 (col1, col2, col3)
    SELECT a.col1, a.col2, a.col3 
    FROM table2 as a
    WHERE NOT EXISTS (SELECT b.col1, b.col2 
                      FROM table1 as b
                      WHERE a.col1 = b.col1 AND a.col2 = b.col2);

I checked around and this seems that it should work but it isn't but can anyone see why?

我查了一下，看来它应该可以工作，但不是，但任何人都可以看到原因吗？

3 个解决方案

#1

I often have trouble when there are two fields to search for. One way is to combine them together:

当有两个字段需要搜索时，我经常遇到麻烦。一种方法是将它们组合在一起：

INSERT INTO table1 (col1, col2, col3)
     SELECT a.col1, a.col2, a.col3 from table2 as a
     WHERE concat(a.col1,':', a.col2) 
     NOT IN (SELECT concat(col1,':',col2) from table1);

Another way is a left join:

另一种方式是左连接：

INSERT INTO table1 (col1, col2, col3)
     SELECT a.col1, a.col2, a.col3 
     from table2 as a
     LEFT OUTER JOIN table1 as b
     ON a.col1 = b.col1
     AND a.col2 = b.col2
     WHERE b.col1 IS NULL AND b.col2 IS NULL;

For example 2, it is better to use a primary key in the where clause.

例如2，最好在where子句中使用主键。

#2

Try this:

尝试这个：

merge into tab2 a
using
(select col1,col2,col3 from tabl1) b
on
(b.col1=a.col1 and b.col2=a.col2)
when not matched then
insert (a.col1,a.col2,a.col3)
values
(b.col1,b.col2,b.col3);

#3

Try this:

尝试这个：

INSERT INTO table1 (col1, col2, col3)
(SELECT a.col1, a.col2, a.col3 
FROM table2 a
WHERE NOT EXISTS (SELECT b.col1, b.col2 
                  FROM table1 b
                  WHERE a.col1 = b.col1 AND a.col2 = b.col2));

I guess for table name as is not needed.

我想不需要表名。