How to write INSERT IF NOT EXISTS queries in standard SQL at Xaprb

How to write INSERT IF NOT EXISTS queries in standard SQL

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.

Written by Xaprb

September 25th, 2005 at 11:48 am

Posted in SQL

22 Responses to 'How to write INSERT IF NOT EXISTS queries in standard SQL'

Subscribe to comments with RSS or TrackBack to 'How to write INSERT IF NOT EXISTS queries in standard SQL'.

I have query.
syntax ‘if not exists’ is not working in oracle 10g. please give solution for it as soon as possible.

Thanks,
Megha

Megha

17 Oct 06 at 2:02 am
Sorry Megha, I am not very familiar with Oracle. You may want to look into the MERGE command.

Xaprb

17 Oct 06 at 7:30 am
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:
```
EMP_TYPE_CODE (PK)
EMPLOYEE_ID (PK)
UPDATE_USERID
UPDATE_TIMESTAMP
EMP_CODE   (PK)
```
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
    )
```
Jon

27 Oct 06 at 6:08 pm
How can we write SQL Server query where we can provide condition of “if not exists” with insert statement?

vineeta

16 Dec 06 at 2:27 am
The same way I wrote in the article, or you may do it this way too:
```
begin transaction;
if not exists(select * from tbl(updlock holdlock) where...)
begin
   insert into tbl...
else
begin
   update tbl...
end
commit;
```
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.
Xaprb

18 Dec 06 at 3:36 pm

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)

Michele Scaramal

27 Feb 07 at 12:10 pm

by the way, the post above applies to oracle!

Michele Scaramal

28 Feb 07 at 4:31 am
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”.

Chris

2 May 07 at 5:41 am
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

Win

9 May 07 at 5:32 pm
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

Universalist (Chris)

21 May 07 at 9:37 pm
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.

Leo Muniz

27 Jun 07 at 6:33 pm
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

Leo Muniz

27 Jun 07 at 8:37 pm
the information given to me was valuable.. thank for the information..

arasu

9 Sep 07 at 12:04 pm
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.

Bogdan

18 Oct 07 at 9:14 am
Awesome solution!!
This saved me so much time that I cannot even estimate.
Thank you very much and congratulations!

Bruno Tarcha

7 Jan 08 at 3:18 pm
Two approaches to update database row if exists, insert if not - http://dotnettipoftheday.org/tips/update-insert-row-sql.aspx

manovich

15 Jan 08 at 7:35 pm
Thanks so much. This really sped up my query in comparison to ‘not exists’.

Sonia

27 Feb 08 at 4:03 pm
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;

Brent

15 Mar 08 at 7:01 pm
I am trying to insert into more than one field using mutex, but getting MySQL errors.

Can anyone help me to find what’s wrong with the following query:

INSERT INTO `users` (`username`, `email`) (SELECT ‘myusername’ FROM `mutex` LEFT OUTER JOIN `users` ON `users`.`username` = ‘myusername’, ‘user@name.com’) WHERE `mutex`.`i` = ‘1′ AND `users`.`username` IS NULL

Thank you in advance!

temuri

25 Aug 08 at 10:31 pm
How about just using:

INSERT INTO table (a) VALUES (1)
ON DUPLICATE KEY UPDATE a=1;

That way you only INSERT when needed and make an empty update otherwise.

Eiso Kant

29 Aug 08 at 5:45 am
Hi I have a similar issue: I have 2 tables t1 and t2
They each have the same columns a,b,c . The primary key is a.
I want to merge the 2 tables using some sort of if not exist statement

t1
aba 1
abb 1
abc 1
abd 1

t2
aba 0
abe 0
abf 1
abg 1
abc 1

all values in table t1 must be preserved as is
only rows from t2 where column values don’t exist in t1 must be inserted, the result should look like. I prefer the resulting table to be t1 rather than t3
I would like to avoid a 3rd table
The values in column c can be any value

t1 or t3
aba 1
abb 1
abc 1
abd 1
abe 0
abf 1
abg 1

What would be a good query

chris

12 Oct 08 at 12:17 am
Correction of errors in previous question

Hi I have a similar issue: I have 2 tables t1 and t2
They each have the same columns a and b . The primary key is a.

I want to merge the 2 tables using some sort of if … not exist statement

t1
aba 1
abb 1
abc 1
abd 1

t2
aba 0
abe 0
abf 1
abg 1
abc 1

All values in table t1 must be preserved. Not deleted. Only rows from t2 where column a values don’t exist in t1 must be inserted, the result should look like.

t1 or t3
aba 1
abb 1
abc 1
abd 1
abe 0
abf 1
abg 1

I prefer the resulting table to be t1 rather than t3, since I would like to avoid a 3rd table. The values in column b can be any value

What’s a good query?

chris

12 Oct 08 at 12:21 am

Xaprb