If necessary, INSERT IF NOT EXISTS queries can be written in a single atomic statement, eliminating the need for a transaction, and without violating standards. In this article I’ll explain several ways to write such queries in a platform-independent way.
Motivation
Suppose a table of urls has a unique index on the url:
create table urls(url varchar(200) primary key);
Now suppose I want to insert a row in a single statement, without using a transaction, and I only want the row to get inserted if the url isn’t already in the table. I don’t want any warnings or errors. I just want to issue a normal INSERT statement and know the url is now in the table.
Standard SQL provides no means of doing this. There’s no syntax like this:
insert if not exists into url(url) values(...)
In Microsoft SQL Server, I can use an IF statement:
if not exists (select * from url where url = ...) insert into url...
This has to be wrapped in a transaction to avoid a race condition, though. Otherwise someone might insert a row between the time I check the table and when I insert the row. I want to do this in a single statement, without a transaction.
Several solutions
One solution is to use a mutex table:
insert into urls(url)
select 'http://www.xaprb.com/blog/'
from mutex
left outer join urls
on urls.url = 'http://www.xaprb.com/blog/'
where mutex.i = 1 and urls.url is null;
There are more flexible variations on this technique. Suppose there is no unique index on the url column. If desired, it is possible to insert several values in a single statement by changing the WHERE clause:
insert into urls(url)
select 'http://www.xaprb.com/blog/'
from mutex
left outer join urls
on urls.url = 'http://www.xaprb.com/blog/'
where mutex.i < 5 and urls.url is null;
Now suppose the requirements specify up to three duplicate entries in the table, and each insert should add a single row. It is possible to insert a row at a time while enforcing the requirement with the following query:
insert into urls(url)
select 'http://www.xaprb.com/blog/'
from mutex
left outer join urls
on urls.url = 'http://www.xaprb.com/blog/'
where mutex.i = 1
group by urls.url
having count(*) < 3;
This query shows the input to the INSERT statement:
select 'http://www.xaprb.com/blog/', count(*)
from mutex
left outer join urls
on urls.url = 'http://www.xaprb.com/blog/'
where mutex.i = 1
group by urls.url;
If this article was useful to you, you should subscribe to stay current with my upcoming articles. It’s free and convenient.
Edit 2006-02-26 See also my article on using this and other techniques to do flexible INSERT and UPDATE statements in MySQL.
Technorati Tags:No Tags
I have query.
syntax ‘if not exists’ is not working in oracle 10g. please give solution for it as soon as possible.
Thanks,
Megha
Sorry Megha, I am not very familiar with Oracle. You may want to look into the MERGE command.
I had a similar issue in oracle and got around it by using derived values from a subquery within an insert statement. Using the assumption that My_Table is defined to have the following fields:
I’ll insert values based off a subquery which checks if the newly inputted values have a count = 0. If the count doesn’t = 0 (the newly inputted values currently exist in that table) then the subquery returns nothing to the INSERT statement and hence there is no insert. This of course returns 0 records affected which you can bubble up to the app level and do with it what you want.
-Jon
INSERT INTO My_Table (SELECT 'FT' AS EMP_TYPE_CODE, 678 AS EMPLOYEE_ID, 'JON' AS UPDATE_USERID, sysdate AS UPDATE_TIMESTAMP, 12345 AS EMP_CODE FROM My_Table WHERE EMP_CODE = 12345 AND TRIM(EMP_TYPE_CODE) = 'FT' AND EMPLOYEE_ID = 678 HAVING COUNT(*) = 0 )How can we write SQL Server query where we can provide condition of “if not exists” with insert statement?
The same way I wrote in the article, or you may do it this way too:
Sorry, I’m a bit rusty on my SQL Server syntax, but that should get you close. The BEGIN and COMMIT are very important, as is the locking hint (updlock holdlock). If you are unclear about this, the SQL Server documentation is an excellent reference.
insert when (condition='true') then into tablela(columna,columnb) values('valuea','valueb') select 'true' as condition from dual where not exists ( Insert your select here to check existence/not existence)by the way, the post above applies to oracle!
My solution to this problem is to just do an “INSERT IGNORE”.
If the Row already exists nothing will happen, if not it will be inserted.
Provided our value is in an unique column it will work perfectly like an “INSERT IF NOT EXISTS”.
Hello Chris I am looking for that exact solution for MySQL but I can’t find the query could you post it again “INSERT IF NOT EXISTS”.
Thanks
Alright, with pleasure.
The query is “insert IGNORE into url(url) values(…)”.
Explanation:
Lets assume the scenario mentioned above.
We want to do the following (impossible): “insert if not exists into url(url) values(…)”
Now, `url` is the primary key, thus has to be unique. This makes mySQL throw an error if we try to insert a value that already exists and, more importantly: the new value won’t be inserted.
Thats practically already an “insert if not exists”. We just have to make mySQL not throw an error if it encounters duplicate unique values, and that’s exactly what “ignore” is for (it has only this purpose, to my knowledge).
So we just do “insert IGNORE into url(url) values(…)” and everybody should be happy :D
Hey Chris,
I would like just to thank you. I’ve been searching for this solution the hole afternoon and now i have it because of you. Thanks.
Hey guys,
I have a doubt. How can I select * except the columns that I specify?
Example:
table users
id_name, description, age
select * except age from users;
i wanna this because i’ll probably need to insert columns at the table and want there are in the select query without having to rewrite it.
thanks
the information given to me was valuable.. thank for the information..
to Chris: IGNORE also ignores several other error conditions (such as charset conversion, invalid values etc). Thus it is advisable to first test the query without IGNORE…
It might be a good alternative to use INSERT … ON DUPLICATE KEY UPDATE syntax with doing nothing in the UPDATE part, but I didn’t test this solution myself.
Awesome solution!!
This saved me so much time that I cannot even estimate.
Thank you very much and congratulations!
Two approaches to update database row if exists, insert if not - http://dotnettipoftheday.org/tips/update-insert-row-sql.aspx
Thanks so much. This really sped up my query in comparison to ‘not exists’.
For MySQL users, this idea might work, although it may be clunky. I’d be happy to hear any means of improving efficiency here!
/*1. Check if record exists. Assign @ifExists the recordID.*/
SELECT @ifExists := ifnull(recordID,0) FROM table where url = ‘http://www.disney.com’;
/*2. Assign @stmt to a valid SQL statement. If recordID = 0, the record doesn’t exist, so use an INSERT statement; otherwise, use a dummy statement. Remember to escape apostrophes within the control clause, as in “\’”.*/
SET @stmt = IF(@ifExists = 0,’INSERT INTO target (field1) values(\’field1 value\’);’,'SELECT null;’);
/*3. Prepare and Execute @stmt*/
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;