mysql insert from one table to another based on select

Supposing we have

Table A

field 1 | field 2    orange  | fruit  apple   | fruit  

and

Table B

field 1 | field 2    orange  | fruit  lemon   | fruit   

And want to insert only the rows from Table B to Table A that don't match on field 1 so that Table A becomes:

Table A

field1  | field2    orange  | fruit  apple   | fruit  lemon   | fruit  

Ive tried

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)  

(no insert)

and

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  

(incorrect insert)

https://stackoverflow.com/questions/65546524/mysql-insert-from-one-table-to-another-based-on-select January 03, 2021 at 12:01PM