MySQL query to select from multiple table and insert in one

Question

There are 5 tables: A, B, C, D, E. Let each table contain an 'email' field. Now we want to INSERT all the emails in A which is present in any of C, D, E but NOT in B.

Sample queries:

CREATE TABLE a (email VARCHAR(100));
CREATE TABLE b (email VARCHAR(100));
CREATE TABLE c (email VARCHAR(100));
CREATE TABLE d (email VARCHAR(100));
CREATE TABLE e (email VARCHAR(100));

INSERT INTO b (email) VALUES ('[email protected]'), ('[email protected]');
INSERT INTO c (email) VALUES ('[email protected]'), ('[email protected]'), ('[email protected]');
INSERT INTO d (email) VALUES ('[email protected]'), ('[email protected]'), ('[email protected]');
INSERT INTO e (email) VALUES ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]');

This is what I tried:

INSERT INTO a (email)
SELECT
c.email
FROM
c
LEFT JOIN b ON c.email = b.email
WHERE b.email IS NULL
UNION DISTINCT
SELECT
d.email
FROM
d
LEFT JOIN b ON d.email = b.email
WHERE b.email
UNION DISTINCT
SELECT
e.email as email
FROM
e
LEFT JOIN b ON e.email = b.email
WHERE b.email IS NULL;

"present in C, D, E", does that mean that an email has to in both C and D and E, or in just any of C, D and E? — jarlh
– jarlh, Commented Jan 27, 2016 at 11:18
Currently we have close to 10 million user in B, 20 million C, 30 million each in D and E. So we need to do it with highly optimized way. — Kumar Nitin
– Kumar Nitin, Commented Jan 27, 2016 at 11:26

Gordon Linoff · Accepted Answer · 2016-01-27 11:28:17Z

1

The following is almost a direct translation of your description:

insert into a(email)
    select email
    from ((select email from c) union
          (select email from d) union
          (select email from e)
         ) cde
    where not exists (select 1 from b where b.email = cde.email);

Note: This uses union intentionally to remove duplicates. If you know there are no duplicates in c, d, e or if you want duplicates, then use union all instead.

answered Jan 27, 2016 at 11:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Abhishek · Accepted Answer · 2016-01-27 11:35:19Z

1

Union all Emails from C,D and E and then discard result which are not present in B:

INSERT INTO a (email)
select * from 
(
    SELECT c.email FROM c
    UNION 
    SELECT d.email FROM d
    UNION 
    SELECT e.email FROM e
) as p where p.email not in ( select email from b);

edited Jan 27, 2016 at 11:35

answered Jan 27, 2016 at 11:28

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL query to select from multiple table and insert in one

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related