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.

Which RDBMS are you using? SQL Server, MySQ, Oracle, MSAccess? — Adriaan Stander, May 09 '13 at 11:13
MySQL although I would like it to work potentially with MSAccess too if poss — harryg, May 09 '13 at 11:16
Check out http://stackoverflow.com/questions/913841/mysql-conditional-insert — Akash KC, May 09 '13 at 11:22
seems ID and date are your logically unique keys. Why don't you declare them unique and handle database exception on unique key constraint violation? — Bilal Mirza, May 09 '13 at 11:23
The approach is fine. However, your sql is not valid as pointed out in Trinimon's answer. You can't have a where clause if you are using the values keyword. — Dan Bracuk, May 09 '13 at 11:25
@BilalMirza ID and date are only unique when combined. I.e. other records can share the date or ID but not both. If this is what you are saying then could you elaborate on how I would do this? — harryg, May 09 '13 at 12:01

Trinimon · Answer 1 · 2019-04-23T12:57:39.233

59

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.

edited Apr 23 '19 at 12:57

answered May 09 '13 at 11:18

Trinimon

13,839
9
44
60

1

How is this different/better than my original method? – harryg May 09 '13 at 11:20
2

This is not different. But it's valid SQL. @harryg have you tried your statement? – ypercubeᵀᴹ May 09 '13 at 11:22
Ok, this one should work now (`FROM dual` was missing). Adjusted my example to fit your code above – Trinimon May 09 '13 at 11:44
@ypercube No I haven't tried my statement as it is still conceptual at this point. So what is the purpose of `dual`? I have googled around but it's not clear how this statement works... – harryg May 09 '13 at 12:13
5

It's exactly what you were trying to do. But MySQL syntax does not allow `WHERE` on an `INSERT ... VALUES` statement. You have to rewrite with `INSERT ... SELECT`. The `dual` is a special table with just one row (and one its uses is like this, to create a table with one row so it can be inserted into `funds`.) – ypercubeᵀᴹ May 09 '13 at 12:15
Yes, `FROM dual` means you select these values as a query result (_from nowhere_ so to say); and selecting values gives you the possibility to add a `WHERE` condition - something that `VALUES` doesn't offer. Alternatively you could use the `ON DUPLICATE KEY` clause, but this requires ID to be a primary key; see updated Fiddle – Trinimon May 09 '13 at 12:37
OK great. I tried your method and it seems to work. Tried it on about 5000 rows and it took around 20s to execute so not the most efficient but adequate for my needs. Thanks for the help. – harryg May 09 '13 at 14:31
that's a lot of variables to keep track of / maintain. @harryg, did you try answer? – gillyspy May 09 '13 at 15:52
@gillyspy not sure what you mean... I tried the answer above and it worked. I was originally looking for a solution that does the same as above but much more efficiently. – harryg May 09 '13 at 16:06
@harryg, gillyspy: if you have an index on ID, both statements are probably the fastest possibilities. May be you can gain some extra performance by executing multiple statements in one transaction (i.e. avoid commits after every statement): check for instance http://stackoverflow.com/questions/5896162/mysql-innodb-insert-performance-windows; also locking tables seems interesting, check out http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html – Trinimon May 09 '13 at 17:18
With sqlite, it might be possible to bypass the need for a "dual" table. Rapid tests give appear to give the expected results with "INSERT INTO funds SELECT 23 as ID, DATE('2013-02-12') as date, 22.5 as price WHERE NOT EXISTS (SELECT * FROM funds WHERE ID = 23 AND date = DATE('2013-02-12'))" – widged Aug 28 '13 at 03:32

harryg · Accepted Answer · 2013-11-13T11:12:03.623

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.

Your solution works well, is much tidier and looks more elegant. Is there a performance difference, though? Doesn't the `ON DUPLICATE KEY` perform a subquery anyway? — Dimitris Sfounis, Nov 13 '14 at 11:27
The performance of UNIQUE depends on the storage engine. In TokuDB, it is discouraged... — gdm, Jan 09 '15 at 13:27

gillyspy · Answer 3 · 2013-05-09T11:37:23.720

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

SQL Insert into table only if record doesn't exist

3 Answers3

FIDDLE

Linked