SQL Insert into table only if record does not exist

Question

I want to run a query set to insert some data into an SQL table, but only if a record is fulfilled that meets certain criteria. The table has 4 fields: id (primary), fund_id , date and price

I have 3 fields in the request: fund_id , date and price .

So my query would look 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' ); 

Therefore, I only want to insert data if the record corresponding to fund_id and date does not exist yet. If the above is true, this seems like a rather inefficient way to achieve this, since an additional select statement must be executed each time.

Is there a better way to achieve the above?

Edit: for clarification, neither fund_id nor date are unique fields; records that have the same fund_id or date file will exist, but no record should have the same fund_id or date as the other.

Answer 1

Although the answer that I initially designated as chosen is correct and achieves what I asked, there is a better way to do this (that others have recognized but not entered). A composite unique index must be created in a table consisting of fund_id and date .

 ALTER TABLE funds ADD UNIQUE KEY `fund_date` (`fund_id`, `date`); 

Then, when inserting a record, add a condition when a conflict occurs:

 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 higher sub-query performance, and the table structure will be higher. It has the caveat that you cannot have NULL values ​​in your unique key columns, as they are still treated as MySQL values.

Answer 2

This might be a simple solution for 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')); 

ps alternatively (if ID 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 script .

Answer 3

Assuming that you cannot modify DDL (to create a unique constraint) or are limited only by the ability to write DML, then check the null value for the result of filtering your values ​​throughout the 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