CODEWITHSUNDEEP
mysqlsqlconditional-statements
PrashanD
PrashanD

Reputation: 2764

MySQL Conditional insert to one table if a certain row does not exist on another table

Is it possible to do an insert query like this?

INSERT INTO `table_1`
VALUES ('val1','val2','val3')
WHERE (
  SELECT COUNT(*)
  FROM `table_2`
  WHERE col1='somevalue'
)=0;

Upvotes: 0

Views: 1678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this using insert . . . select:

INSERT INTO `table_1`(col1, col2, col3)
    SELECT col1, col2, col3
    FROM (SELECT 'val1' as col1,'val2' as col2, 'val3' as col3) t
    WHERE NOT EXISTS (SELECT 1
                      FROM table_2 t2
                      WHERE t2.col1 = 'somevalue'
                     );

Notes:

  • This seems really strange. Limiting inserts into table_1 when no row exists in table_2 with other values.
  • NOT EXISTS is more efficient than using COUNT(*).
  • You should always list the columns when using INSERT.

Upvotes: 4

Related Questions