CODEWITHSUNDEEP
mysqlsqlinsert
Ben
Ben

Reputation: 1011

How to insert data from one table to another with conditions

I am trying to insert new rows into table a and partially load the rows with data from table b. I would only like to insert new rows where the field b.id is not present in the a.id column.

Here are my tables:

table_a       table_b
---------     -----------
id             id
first_name     first_name
country        middle_name
last_name      last_name

Here is my code:

INSERT INTO table_a a
SELECT b.id, b.first_name,b. last_name
FROM table_b b WHERE b.id <> a.id

The Question: How can I do this using an insert select statement? Please note, I've included middle name in one table and Country in another table to make this more comparable to my current situation. Is there a way to "map" the fields? or must I ensure that my insert select statement has the exact same number of values to insert as the target table? Or will it just look for matching column names and only update those columns that match?

Upvotes: 2

Views: 31566

Answers (3)

John Woo
John Woo

Reputation: 263723

I would only like to insert new rows where the field b.id is not present in the a.id column.

an alternative solution would be using LEFT JOIN and IS NULL

INSERT INTO table_a
SELECT  b.*
FROM    table_b b
        LEFT JOIN table_a a
            ON a.ID = b.ID
WHERE   a.ID IS NULL

Upvotes: 3

rs.
rs.

Reputation: 27427

You can do following, check if id exists in table_a

INSERT INTO table_a (id, first_name, last_name)
SELECT b.id, b.first_name,b.last_name
FROM table_b b
WHERE not exists (SELECT 1 FROM table_a a WHERE b.id = a.id)

Your country column will be null for all the rows. If you want to store static value then query should be

INSERT INTO table_a (id, first_name, country, last_name)
SELECT b.id, b.first_name, 'USA', b.last_name
FROM table_b b
WHERE not exists (SELECT 1 FROM table_a a WHERE b.id = a.id)

Upvotes: 2

echo_Me
echo_Me

Reputation: 37233

try this

   INSERT INTO table_a a (id , first_name , last_name) Values (
                SELECT b.id, b.first_name,b.last_name
                FROM table_b b WHERE  a.id not in (select id from table_b) )

Upvotes: -1

Related Questions