mysqljoinsql-insertmysql-select-db

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

Solution

  • 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