SQL Insert into table only if record doesn t exist

Question

I want to run a set of queries to insert some data into an SQL table but only if the record satisfying certain criteria are met  The table has 4 fields  id  primary   fund id  date and price  I have 3 fields in the query  fund id  date and price   So my query would go something like this   INSERT INTO funds  fund id  date  price      VALUES  23   2013-02-12   22 43      WHERE NOT EXISTS          SELECT           FROM funds         WHERE fund id   23          AND date    2013-02-12           So I only want to insert the data if a record matching the fund id and date does not already exist  If the above is correct it strikes me as quite an inefficient way of achieving this as an additional select statement must be run each time   Is there a better way of achieving the above   Edit  For clarification neither fund id nor date are unique fields  records sharing the same fund id or date will exist but no record should have both the same fund id and date as another

User · Answer

Assuming you cannot modify DDL  to create a unique constraint  or are limited  to only being able to write DML then check for a null on filtered result of your values against the whole table  FIDDLE  insert into funds  ID  date  price   select      T    from       select 23 ID    2013-02-12  date   22 43 price  T           left join      funds on funds ID   T ID and funds date   T date where      funds ID is null

User · Answer

Although the answer I originally marked as chosen is correct and achieves what I asked there is a better way of doing this  which others acknowledged but didn t go into   A composite unique index should be created on the table consisting of fund id and date   ALTER TABLE funds ADD UNIQUE KEY  fund date    fund id    date      Then when inserting a record add the condition when a conflict is encountered   INSERT INTO funds   fund id    date    price       VALUES  23  DATE  2013-02-12    22 5          ON DUPLICATE KEY UPDATE  price     price   --this keeps the price what it was  no change to the table  or   INSERT INTO funds   fund id    date    price       VALUES  23  DATE  2013-02-12    22 5          ON DUPLICATE KEY UPDATE  price    22 5  --this updates the price to the new value   This will provide much better performance to a sub-query and the structure of the table is superior  It comes with the caveat that you can t have NULL values in your unique key columns as they are still treated as values by MySQL

User · Answer

This might be a simple solution to achieve this   INSERT INTO funds  ID  date  price  SELECT 23  DATE  2013-02-12    22 5   FROM dual  WHERE NOT EXISTS  SELECT 1                       FROM funds                      WHERE ID   23                       AND date   DATE  2013-02-12       p s  alternatively  if ID a primary key     INSERT INTO funds  ID  date  price      VALUES  23  DATE  2013-02-12    22 5          ON DUPLICATE KEY UPDATE ID   23  -- or whatever you need   see this Fiddle

[mysql] SQL Insert into table only if record doesn't exist

FIDDLE

Examples related to mysql

Examples related to sql

Examples related to sql-insert