CODEWITHSUNDEEP
mysqlsqlselectinsertexists
blasibr
blasibr

Reputation: 25

SQL Select/insert a row from another table if it doesn't exist in one table

I have one table that may be missing IDs so I need to look to another table. And where there are missing IDs I need to insert a row based on current table but put qty field to 0. Not sure if this is possible...

Table A:

ID--F1--QTY
1 --xx--5
2 --xx--6
3 --xx--7

Table B:

ID
4

New Table:

ID--F1--QTY
1 --xx--5
2 --xx--6
3 --xx--7
4 --xx--0

Upvotes: 0

Views: 1833

Answers (2)

sticky bit
sticky bit

Reputation: 37472

Another option is a correlated subquery and NOT EXITS.

INSERT INTO `Table A`
            (`ID`,
             `QTY`)
            SELECT `ID`,
                   0
                   FROM `Table B`
                   WHERE NOT EXISTS (SELECT *
                                            FROM `Table A`
                                            WHERE `Table A`.`ID` = `Table B`.`ID`);

Upvotes: 1

rs.
rs.

Reputation: 27427

You can use joins or NOT IN to do this,

Using Joins

Join tableB to tableA and pick all the Ids that don't exist and then insert that into tableA

INSERT INTO TableA(ID, QTY)
SELECT X.ID, 0 QTY FROM TableB X
LEFT OUTER JOIN TableA Y ON X.ID = Y.ID
WHERE Y.ID IS NULL

OR

If you want to create a new table with data from both tables then use following SQL

INSERT INTO NewTable(ID, QTY)
SELECT X.ID, 0 QTY FROM TableB X
LEFT OUTER JOIN TableA Y ON X.ID = Y.ID
WHERE Y.ID IS NULL
UNION ALL
SELECT ID, QTY FROM TableA

Using NOT IN

INSERT INTO TableA(ID, QTY)
SELECT X.ID, 0 QTY FROM TableB X
WHERE X.ID NOT IN (SELECT ID FROM TableA WHERE ID IS NOT NULL)

Upvotes: 4

Related Questions