SQL Insert into table only if record doesn't exist

harryg picture harryg · May 9, 2013 · Viewed 248.3k times · Source

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.

Answer

Trinimon picture Trinimon · May 9, 2013

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.