首页 文章

SQL NOT EXISTS - 不重复插入

提问于
浏览 次
1

我有2个表，具有不同的字段名称，但数据相同 . 我想将另一个表中的数据导入到一个表中而不生成重复的行 .

这是我尝试过的（我在Access VBA中工作）：

Private Sub Command4_Click()

Dim SQL As String

SQL = "INSERT INTO COMPANY(CompanyNumber, CompanyName)" & _
            " SELECT Number, Company" & _
            " FROM NEW" & _
            " WHERE NOT EXISTS " & _
            "(SELECT CompanyNumber, CompanyName FROM COMPANY)" 

    DoCmd.RunSQL SQL

End Sub

这让我追加0行 . 我究竟做错了什么 ？

vba ms-access

4 回答

  • 0

    在表（table_1）上创建一个唯一索引，您要在其中插入必须唯一的所有列 . 然后执行此操作 . 列的名称并不重要，但字段的顺序必须相同 . 如果不是，则必须指定表2中列的顺序 . 将*更改为fieldname1，fieldname3，fieldname2，...

    INSERT IGNORE INTO table_1
  SELECT * FROM table_2;
    回复于
  • 0

    如果您出于某种原因不想在（AField1，AField2）上创建唯一键，则只需将 SELECT 1 FROM TablA WHERE 添加到 NOT EXISTS 部分：

    INSERT INTO TableA (AField1, AField2)
    SELECT BField1,Bfield2
    FROM TableB
    WHERE NOT EXISTS (
        SELECT 1 FROM TableA
        WHERE TableB.BField1 = TableA.AField1 AND TableB.BField2 = TableA.AField2
    )

    您还可以尝试排除LEFT JOIN：

    INSERT INTO TableA (AField1, AField2)
    SELECT BField1,Bfield2
    FROM TableB
    LEFT JOIN TableA ON TableB.BField1 = TableA.AField1 AND TableB.BField2 = TableA.AField2
    WHERE TableA.AField1 IS NULL
    回复于
  • 0

    Yesssss，我终于让它工作了，这是一整天的噩梦 . 这里是：

    Private Sub Command4_Click()

Dim SQL As String

SQL = "INSERT INTO COMPANY(CompanyNumber, CompanyName)" & _
            " SELECT DISTINCT Number, Company" & _
            " FROM NEW" & _
            " WHERE NOT EXISTS " & _
            "(SELECT * FROM COMPANY WHERE" & _
            " (NEW.Number=COMPANY.CompanyNumber AND NEW.Company=COMPANY.CompanyName)" 

    DoCmd.RunSQL SQL

End Sub

    @DylanSue感谢所有其他人，你帮助了我很多 . 在进行重复操作时，MS-access看起来很奇怪 . 我添加了 DISTINCT 因为第二个表有许多相同的条目 . 但是，如果我执行两次相同的代码，它会再次导入2行 - 其中一行在两列上都是空的，而在一列中只有一行 . 如何避免两列的空白条目？ （如果一列是空白，那么它也应该导入） .

    回复于
  • 0

    自MS-ACCESS以来，解决方案变得更加复杂 .

    此外，为了很好地处理NULL，添加了更多的过滤器 . 对于NULL，NULL行，它不会被插入到TableA中，而xx，NULL将被插入 .

    简而言之，所需的INSERT INTO ... SELECT语句如下：

    INSERT INTO TableA(Company_Number, Company_Name)
SELECT 
    DISTINCT Company_Number, Company_Name
FROM
    TableB
WHERE NOT EXISTS
            (SELECT 
                1
             FROM 
                TableA
             WHERE
                (TableA.Company_Number=TableB.Company_Number OR (TableA.Company_Number IS NULL AND TableB.Company_Number IS NULL)) AND 
                (TableA.Company_Name=TableB.Company_Name OR (TableA.Company_Name IS NULL AND TableB.Company_Name IS NULL)) AND
                NOT (TableB.Company_Number IS NULL AND TableB.Company_Name IS NULL));

    附上一个完整的演示 . （我没有MS-ACCESS的环境，这在MySQL 5.7中执行） .

    SQL：

    create table TableA(ID_Number int auto_increment primary key, Company_Number int, Company_Name varchar(200));
create table TableB(Company_Number int, Company_Name varchar(200));

insert into TableA(Company_Number, Company_Name) values
(1,'A'),
(2,'B');
insert into TableB(Company_Number, Company_Name) values
(2,'B'),
(3,'C'),
(4,NULL),
(NULL,'D');
SELECT * FROM TableA;
SELECT * FROM TableB;
INSERT INTO TableA(Company_Number, Company_Name)
SELECT 
    DISTINCT Company_Number, Company_Name
FROM
    TableB
WHERE NOT EXISTS
            (SELECT 
                1
             FROM 
                TableA
             WHERE
                (TableA.Company_Number=TableB.Company_Number OR (TableA.Company_Number IS NULL AND TableB.Company_Number IS NULL)) AND 
                (TableA.Company_Name=TableB.Company_Name OR (TableA.Company_Name IS NULL AND TableB.Company_Name IS NULL)) AND
                NOT (TableB.Company_Number IS NULL AND TableB.Company_Name IS NULL));
SELECT * FROM TableA;

INSERT INTO TableA(Company_Number, Company_Name)
SELECT 
    DISTINCT Company_Number, Company_Name
FROM
    TableB
WHERE NOT EXISTS
            (SELECT 
                1
             FROM 
                TableA
             WHERE
                (TableA.Company_Number=TableB.Company_Number OR (TableA.Company_Number IS NULL AND TableB.Company_Number IS NULL)) AND 
                (TableA.Company_Name=TableB.Company_Name OR (TableA.Company_Name IS NULL AND TableB.Company_Name IS NULL)) AND
                NOT (TableB.Company_Number IS NULL AND TableB.Company_Name IS NULL));
SELECT * FROM TableA;

    输出：

    mysql> SELECT * FROM TableA;
+-----------+----------------+--------------+
| ID_Number | Company_Number | Company_Name |
+-----------+----------------+--------------+
|         1 |              1 | A            |
|         2 |              2 | B            |
+-----------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM TableB;
+----------------+--------------+
| Company_Number | Company_Name |
+----------------+--------------+
|              2 | B            |
|              3 | C            |
|              4 | NULL         |
|           NULL | D            |
+----------------+--------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO TableA(Company_Number, Company_Name)
    -> SELECT
    ->     DISTINCT Company_Number, Company_Name
    -> FROM
    ->     TableB
    -> WHERE NOT EXISTS
    ->             (SELECT
    ->                 1
    ->              FROM
    ->                 TableA
    ->              WHERE
    ->                 (TableA.Company_Number=TableB.Company_Number OR (TableA.Company_Number IS NULL AND TableB.Company_Number IS NULL)) AND
    ->                 (TableA.Company_Name=TableB.Company_Name OR (TableA.Company_Name IS NULL AND TableB.Company_Name IS NULL)) AND
    ->                 NOT (TableB.Company_Number IS NULL AND TableB.Company_Name IS NULL));
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TableA;
+-----------+----------------+--------------+
| ID_Number | Company_Number | Company_Name |
+-----------+----------------+--------------+
|         1 |              1 | A            |
|         2 |              2 | B            |
|         3 |              3 | C            |
|         4 |              4 | NULL         |
|         5 |           NULL | D            |
+-----------+----------------+--------------+
5 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO TableA(Company_Number, Company_Name)
    -> SELECT
    ->     DISTINCT Company_Number, Company_Name
    -> FROM
    ->     TableB
    -> WHERE NOT EXISTS
    ->             (SELECT
    ->                 1
    ->              FROM
    ->                 TableA
    ->              WHERE
    ->                 (TableA.Company_Number=TableB.Company_Number OR (TableA.Company_Number IS NULL AND TableB.Company_Number IS NULL)) AND
    ->                 (TableA.Company_Name=TableB.Company_Name OR (TableA.Company_Name IS NULL AND TableB.Company_Name IS NULL)) AND
    ->                 NOT (TableB.Company_Number IS NULL AND TableB.Company_Name IS NULL));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TableA;
+-----------+----------------+--------------+
| ID_Number | Company_Number | Company_Name |
+-----------+----------------+--------------+
|         1 |              1 | A            |
|         2 |              2 | B            |
|         3 |              3 | C            |
|         4 |              4 | NULL         |
|         5 |           NULL | D            |
+-----------+----------------+--------------+
5 rows in set (0.00 sec)
    回复于

相关问题