Insert data from a mysql table into another with php, skipping the ones already present-CodePudding

Insert data from a mysql table into another with php, skipping the ones already present

Hi everyone, I can't solve this problem of mine:

I have 2 table:

table_1 (id, cod.products, color, price, id_supplier)

table_2 (id_supplier, cod.products, color, price)

I would like to insert the data of table_2 into table_1 only if id_supplier of table_2 is not present in id_supplier of table1

I tried doing several queries:

with this

    INSERT INTO table_1 (cod.products, color, price, id_supplier) 
SELECT cod.products, color, price, id_supplier FROM table_2

I insert everything without taking into account the id_supplier ...

trying where clause doesn't work ...

    INSERT INTO table_1 (cod.products, color, price, id_supplier) 
SELECT cod.products, color, price, id_supplier FROM table_2 
WHERE table_1.id_supplier <> table_1.id_supplier

CodePudding user response：

WHERE table_1.id_supplier <> table_1.id_supplier

change to

WHERE table_2.id_supplier NOT IN (SELECT id_supplier FROM table_1 GROUP BY id_supplier)

CodePudding user response：

Use the NOT IN operator.

INSERT INTO table_1 (cod.products, color, price, id_supplier) 
SELECT cod.products, color, price, id_supplier 
FROM table_2 where id_supplier not in (select distinct id_supplier from table_1)
;