将唯一数据从一个表复制到另一个表 [英] Copy unique data from one table to another

问题描述

我有两个桌子. Table A是主表，table B是辅助表.现在我只需要将aux_id中不存在的带有aux_id的行从table B复制到table A

表_A

id |aux_id | firstname | lastname

table_B

 aux_id | firstname | lastname

我正在尝试，但是显然没有成功.

INSERT INTO table_A(aux_id,firstname,lastname)(SELECT aux_id,firstname,lastname FROM table_B WHERE aux_id != aux_id);

我正在获取:INSERT 0 0

这是错误的，因为我确定我在表B中有新条目.

解决方案

有很多方法可以做到这一点(left join，not exists，not in).如果您正在学习SQL，NOT IN可能是初次接触时最容易的方法:

INSERT INTO table_A(aux_id, firstname, lastname)
    SELECT aux_id, firstname, lastname
    FROM table_B
    WHERE b.aux_id NOT IN (SELECT a.aux_id FROM table_A a);

我应该说我更喜欢NOT EXISTS:

    WHERE NOT EXISTS (SELECT 1 FROM table_A a WHERE a.aux_id = b.aux_id);

原因与NULL值的处理有关. NOT EXISTS可以完成您的期望.如果子查询返回NULL值，则NOT IN永远不会返回true.

I have two tables. Table A is the main table, and table B an auxiliary table. Now i need to copy from table B to table A only the rows with a aux_id not present in table A

table_A

id |aux_id | firstname | lastname

table_B

 aux_id | firstname | lastname

What I am trying, but apparently without success.

INSERT INTO table_A(aux_id,firstname,lastname)(SELECT aux_id,firstname,lastname FROM table_B WHERE aux_id != aux_id);

I am getting : INSERT 0 0

This is wrong, because i am sure that I have new entries in table B.

解决方案

There are many ways to do this (left join, not exists, not in). If you are learning SQL, NOT IN, might be the easiest on first encounter:

INSERT INTO table_A(aux_id, firstname, lastname)
    SELECT aux_id, firstname, lastname
    FROM table_B
    WHERE b.aux_id NOT IN (SELECT a.aux_id FROM table_A a);

I should say that I much prefer NOT EXISTS:

    WHERE NOT EXISTS (SELECT 1 FROM table_A a WHERE a.aux_id = b.aux_id);

The reason has to do with the handling of NULL values. The NOT EXISTS does what you expect. NOT IN never returns true if the subquery returns a NULL value.

这篇关于将唯一数据从一个表复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！