mysql根据select从一个表插入到另一个表

Question

假设我们有

表A

field 1 | field 2

orange  | fruit
apple   | fruit

和

表B

field 1 | field 2

orange  | fruit
lemon   | fruit 

并且只想将表 B 中与字段 1 不匹配的行插入到表 A 中，以便表 A 变为:

表A

field1  | field2

orange  | fruit
apple   | fruit
lemon   | fruit

我试过了

insert into tableA (field1, field2)
select field1, field2 from tableB
where not exists 
(select * from tableA as a join tableB as b on a.field1 = b.field1)

(无插入)

和

insert into tableA (field1, field2)
select field1, field2  from tableA as a left join tableB as b on a.field1 = b.field1  
union 
select field1, field2  from tableA as s right join tableB as b on a.field1 = b.field1 
where a.field1 != b.field1

(插入不正确)

Answer 1

您的 NOT EXISTS 表达式不正确，它需要查看您尝试插入的 tableB 中的值:

INSERT INTO tableA (field1, field2)
SELECT field1, field2 
FROM tableB b
WHERE NOT EXISTS (SELECT * FROM tableA a WHERE a.field1 = b.field1)

此查询后 SELECT * FROM tableA 的输出:

field1  field2
orange  fruit
apple   fruit
lemon   fruit

Demo on db-fiddle