CODEWITHSUNDEEP
mysqljoinsql-insertmysql-select-db
Kaizar Laxmidhar
Kaizar Laxmidhar

Reputation: 869

Insert query depend on another table

Below are MySQL tables. I need to insert values from table A's ID column to Table B's ID_A column. Already existed values in ID_A should be ignored. So that in the below mentioned example IDs 1 to 3 from Table A should be ignored and IDs 4 to 10 should be inserted in table B. ID_X's value is constant i.e. 3.

What would be a single insert SQL query?

enter image description here

Upvotes: 2

Views: 2207

Answers (4)

Blank
Blank

Reputation: 12378

Try this;)

insert into tableb (id_a, id_x)
select a.id as id_a, 3 as id_x from tablea a join tableb b on b.id_a <> a.id

Upvotes: 0

apomene
apomene

Reputation: 14389

INSERT INTO TABLEB
(ID_A,ID_X)
SELECT
     ID,
     3
FROM TABLEA
WHERE ID NOT IN (SELECT ID_A FROM TABLEB)

Upvotes: 1

piotrgajow
piotrgajow

Reputation: 2950

You could create a unique index on column ID_A, and then run query:

INSERT IGNORE INTO table_b
    SELECT      ID,
                3
        FROM    table_a;

This should add the missing rows.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

One straightforward option is to left join A with B and insert only those records from A which do not already appear in B.

INSERT INTO B (ID_A, ID_X)
SELECT A.ID, 3
FROM A LEFT JOIN B
   ON A.ID = B.ID_A
WHERE B.ID_A IS NULL

Upvotes: 2

Related Questions