CODEWITHSUNDEEP
mysqlsql
Kumar Nitin
Kumar Nitin

Reputation: 1863

MySQL query to select from multiple table and insert in one

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;

Upvotes: 0

Views: 51

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

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);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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.

Upvotes: 1

Related Questions