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.
This question is related to
mysql
sql
sql-insert
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
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
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.
Source: Stackoverflow.com