SQL Insert into table only if record doesn't exist

MysqlSqlSql Insert

Mysql Problem Overview


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.

Mysql Solutions


Solution 1 - Mysql

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.

Solution 2 - Mysql

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.

Solution 3 - Mysql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionharrygView Question on Stackoverflow
Solution 1 - MysqlTrinimonView Answer on Stackoverflow
Solution 2 - MysqlharrygView Answer on Stackoverflow
Solution 3 - MysqlgillyspyView Answer on Stackoverflow