MYSQL - Multiple value insert into table where not exists in another

Question

I have PHP array(array has multiple values) and i want to add that data in table but before insert into table check for value is exist in another table, if value get in another table skip that value.

Example:

INSERT INTO table1('column1','column2','column3') VALUES
  ('val11','val11','val11'),
  ('val12','val12','val12')
    WHERE NOT EXIST 
      (SELECT * FROM table2 WHERE table2.column1 = VALUES(column1) AND 
        table2.column2 = VALUES(column2) AND table2.column3 = VALUES(column3)

Am I on the right path or is there a better way of doing this?

Thanks

Miten

Possible duplicate of [MySQL Conditional Insert](https://stackoverflow.com/questions/913841/mysql-conditional-insert) — Nick, Dec 03 '18 at 11:45

score 0 · Answer 1 · answered Dec 03 '18 at 12:06

You need to use INSERT ... SELECT syntax to achieve this result. Because you have multiple rows you have to UNION the data rows into one derived table, and then check that the values in that table don't exist in table2:

INSERT INTO table1 (column1, column2, column3)
SELECT *
FROM (SELECT 'val11' AS column1, 'val12' AS column2, 'val13' AS column3
      UNION
      SELECT 'val21', 'val22', 'val23') v
WHERE NOT EXISTS (SELECT * 
                  FROM table2 t2
                  WHERE t2.column1 = v.column1
                    AND t2.column2 = v.column2
                    AND t2.column3 = v.column3)

Demo on dbfiddle

MYSQL - Multiple value insert into table where not exists in another

1 Answers1