mysql insert from one table to another based on select

Question

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)

Something like: INSERT INTO tableA (field1, field2) SELECT b.field1, b.field2 FROM tableA a LEFT JOIN tableB b USING field1 WHERE a.field1 IS NULL I think is what you're looking for. Start off by figuring out the SELECT that you want (all rows from tableB that are not in tableA), and then insert only those rows. — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented Jan 3, 2021 at 4:06
question uri trymysql:lab2017:basicops:uu579distantaystipulate — dreftymac
– dreftymac, Commented Feb 4, 2021 at 21:04

Nick · Accepted Answer · 2021-01-03 04:06:42Z

3

You don't have your NOT EXISTS expression quite right, it needs to look at the value from tableB that you are trying to insert:

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

Output of SELECT * FROM tableA after this query:

field1  field2
orange  fruit
apple   fruit
lemon   fruit

Demo on db-fiddle

answered Jan 3, 2021 at 4:06

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PushpikaWan · Accepted Answer · 2021-01-03 04:08:14Z

2

you can simply use union for that or use below code

INSERT INTO Table A SELECT * FROM Table B
WHERE NOT EXISTS (SELECT 1 FROM TABLE A Data
                  WHERE A.field1 = Data.field1 AND 
                  A.field2 = Data.field2)

answered Jan 3, 2021 at 4:08

PushpikaWan

2,5853 gold badges18 silver badges25 bronze badges

Comments

Ashish Tuteja · Accepted Answer · 2021-01-03 04:18:08Z

1

This is probably not the best solution, but this solution using joins worked for me:

INSERT INTO A(F1,F2)
SELECT * 
FROM A NATURAL RIGHT JOIN B AS TAB
WHERE TAB.F1 NOT IN (
SELECT F1
FROM A NATURAL JOIN B
);

answered Jan 3, 2021 at 4:18

Ashish Tuteja

1686 bronze badges

Collectives™ on Stack Overflow

mysql insert from one table to another based on select

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related