How to write INSERT IF NOT EXISTS queries in standard SQLSun, Sep 25, 2005 in Databases
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.
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 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.
One solution is to use a mutex table:
insert into urls(url) select '/blog/' from mutex left outer join urls on urls.url = '/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
insert into urls(url) select '/blog/' from mutex left outer join urls on urls.url = '/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 '/blog/' from mutex left outer join urls on urls.url = '/blog/' where mutex.i = 1 group by urls.url having count(*) < 3;
This query shows the input to the
select '/blog/', count(*) from mutex left outer join urls on urls.url = '/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.
About The Author
Baron is the founder and CEO of VividCortex. He is the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. Baron contributes to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.