How to update all the columns in current table if some records are missing?-CodePudding

I need to update my Product table. I need to add all the products which are not in Product table by comparing the Catalogue table. My tables are as;

Product table
ProductID ProductName Quantity
1.        A.          3
2.        B.          2
3.        C.          3
Catalogue table
CatelogueID ProductID ProductName Quantity
1.          3.        C.          3
2.          4.        D.          1
3.          1.        A.          3
4.          6.        F.          5
5.          2.        B.          1
6.          5.        E.          0

CodePudding user response：

In order to get the new row values to insert into the product table, you need to get all the rows from the catalogue table, each product of which does not exist in the product table.

And yout query would be like this

INSERT INTO product  
SELECT productid, productname, quantity 
FROM catalogue c
WHERE NOT EXISTS (
    SELECT 1 FROM product p WHERE p.productid = c.productid
);

Demo