2

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

Kaizar Laxmidhar
  • 859
  • 1
  • 17
  • 38

4 Answers4

2

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

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.

piotrgajow
  • 2,880
  • 1
  • 22
  • 23
1
INSERT INTO TABLEB
(ID_A,ID_X)
SELECT
     ID,
     3
FROM TABLEA
WHERE ID NOT IN (SELECT ID_A FROM TABLEB)
apomene
  • 14,282
  • 9
  • 46
  • 72
  • Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight May 31 '16 at 12:04
0

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
Blank
  • 12,308
  • 1
  • 14
  • 32