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.

Further Reading:

Written by Xaprb

September 25th, 2005 at 11:48 am

Posted in Uncategorized

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

Subscribe to comments with RSS

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
How to write a select statement such that returned values are the ones that don’t exist in the table?

eg. Assume that in nameTable, I have records for ‘jak’ and ‘mike’.

But, I want ‘tim’ and ‘joe’ listed in the sql output. (not jak and mike)

If I issue this sql statement:

select firstname from nameTable where firstname in (‘jak’, ‘mike’, ‘tim’, ‘joe’);

I will get jak and mike in the output, but I want tim and joe.

How to do this?
Thanks

Nepali Games

12 Feb 09 at 10:57 am
Hello Xaprb,

Is it possible to insert multiple values in a query with this mutex table way?

Thanks,
Sancar

sancar

23 Mar 09 at 4:39 pm
Insert Into ProductCategory (ProductCategoryID)
Select ‘KEY’
From ProductCategory
Where ProductCategoryID = ‘KEY’
Having Count(*) = 0

noezavala

25 Mar 09 at 2:15 pm
SQLSERVER 2005

INSERT INTO TABLENAME (COL1,COL2,COL3)
SELECT ‘VAL1′,’VAL2′,’VAL3′
WHERE NOT EXISTS (SELECT 1
FROM TABLENAME/DIFFNAME
WHERE A = ‘B’
AND C = ‘D’)

Jimbo

1 Apr 09 at 10:11 am
Nice post. Can i use this for mysql.

VINKAS India

30 Apr 09 at 5:21 am
Just to say THANKS to chris ;)

Hello

24 Sep 09 at 2:56 pm
[...] a final notice: this post was inspired by Xaprb. I'd also advise to consult his other post on writing flexible SQL [...]

MySQL: INSERT IF NOT EXISTS syntax

6 Oct 09 at 5:28 pm
thx noezavala, your query works very well in mysql.

i have added some more attributes so if someone needs a bit modulated version and cant handle it:

INSERT INTO recode(id_project, id_question, basenum, rangn, reconum) SELECT id_project, id_question, basenum, rangn, reconum
FROM recode WHERE id_project= ’5′ AND id_question=’6′ AND basenum=’1′ AND reconum=’1′ HAVING Count(*) = 0

Erik ?erpnjak

4 Nov 09 at 3:22 pm
thx noezavala, your query works very well in mysql.

i have added some more attributes so if someone needs a bit modulated version and cant handle it:

INSERT INTO recode(id_project, id_question, basenum, rangn, reconum) SELECT id_project, id_question, basenum, rangn, reconum
FROM recode WHERE id_project= ’5′ AND id_question=’6′ AND basenum=’1′ AND reconum=’1′ HAVING Count(*) = 0

Erik ?erpnjak

4 Nov 09 at 3:23 pm
Seems to me that only Jon’s solution works for Oracle. Thanks!

gunther

16 Dec 09 at 6:20 am
SQLSERVER 2005

INSERT INTO TABLENAME (COL1,COL2,COL3)
SELECT ‘VAL1?,’VAL2?,’VAL3?
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM TABLENAME/DIFFNAME
WHERE A = ‘B’ AND C = ‘D’)

dario

1 Jan 10 at 6:55 pm
i want to know about oracle 10g.
if table does not exist then how should i come to know by query that it does not exist,is there any function which return true or false when table will not exist?
can u plz tell me ?

namrata

7 Mar 10 at 8:23 am
Folks, none of the solutions proposed so far appears to satisfy the following criteria:

1. Works with one table in the database.
2. Works with all or most SQL database software.
3. Can be executed as a single SQL statement.

I seem to have found a solution that satisfies these criteria, but I confess I am no SQL expert, and I cobbled this statement together after much trial and error.

Assume column a is the primary key for table x:

INSERT INTO x (SELECT ‘a’ as a, ‘b’ as b, ‘c’ as c FROM (SELECT count(*) FROM x) WHERE a NOT IN (SELECT a FROM x))

I have no idea what the performance characteristics of this statement are.

Roger

28 Apr 10 at 2:05 am
Just a follow-up on the solution I posted in my last comment. I have been using it since the comment, and it has been working well. I’ve observed no apparent performance issues or other problems.

Roger

10 Jun 10 at 4:16 pm
I’m a newbie on sql server so in an my vb net application I have used anoher way:
IF NOT (SELECT COUNT (*) FROM … WHERE …. like ‘…..’) > 0
INSERT INTO …..
” (…..) VALUES (‘……’)

Seems to works very well for ntext values.

Bye
Rud

Rudi

14 Jun 10 at 5:17 am
hello people ,

I have slightly different challenge .

I have a table where i am using two columns (as primary keys)to determine whether a row is a duplicate or not . However there are other tables which have information from which i can determine whether the row in my initial table is a duplicate or not.

my question is after determining that a certain row is not an actual duplicate , how can i go about changing the value for the key columns to the initial column values incremented ?

sam king

25 Jun 10 at 3:08 pm
in SQL Server 2008 you could also use something like this:

MERGE INTO Sales.SalesReason AS Target
USING (VALUES (‘Recommendation’,'Other’), (‘Review’, ‘Marketing’), (‘Internet’, ‘Promotion’))
AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

chibisuke

25 Jul 10 at 7:58 pm
Glad I found this post. Using mutex table allowed me to do UPDATE/INSERT without wasting the auto-num field. Other alternatives weren’t as kind.

Thanks.

Alvin

27 Sep 10 at 1:12 am
The solution I provided in my April 28th comment worked flawlessly for me until I tried it using version 2.0 of HSQLDB. HSQLDB 2.0 spit out the following error:

“user lacks privilege or object not found: A”

The following slight tweak fixed the problem:

INSERT INTO x (SELECT ‘a’ as a, ‘b’ as b, ‘c’ as c FROM (SELECT ‘a’ as a, count(*) FROM x) WHERE a NOT IN (SELECT a FROM x))

Roger

6 Jan 11 at 12:36 pm
i want to insert value in a table which is not exist already in it.i had defined a column autoincremanted primary key thr.i want if user want to enter already exising value in it,it wont be inserted,else it will be inserted.
please help me

neha

21 Jan 11 at 3:02 am
in oracle you can use something, it will check whether data is present or not and insert into the table. ( any issue contact me at forroughuse3@gmail.com)

INSERT INTO EMP(empno,ename,job,mgr,hiredate,sal,comm,deptno)
SELECT 8000,’VIPUL’,'CS’,7741,to_DATE(’25-SEP-1982′,’DD-MON-YYYY’),5000,100,10
FROM DUAL
WHERE NOT EXISTS(SELECT * FROM emp WHERE empno=8000);

Regards,
Vipul V

Vipul

10 Feb 11 at 12:43 am
table: unique key ip (ip)

insert into t (f1, ip) values(22, ’127.0.0.1′) on duplicate key update i= last_insert_id(i), f3= f3 + 1;

Yousha

15 Feb 11 at 12:43 pm
Hey all people

I have the PERFECT solution for this case:
Use REPLACE:

1. replace into table set column1=’value1′, column2=’value1′, …

2. replace into table (column1, column2, …) values (‘value1′,’value2′, …)

REPLACE sencente modify values of a table if exists (first deleting the old values and inserts the new information) . If not, it inserts automatically

Pancho2413

12 Apr 11 at 7:43 pm
Hi guys

I have the below syntax but it is always insert duplicates can someone please help

INSERT INTO roshcon_vehicle_v1_0
(VehicleRegNumber,VehicleType,VehicleMake,driver_FstName,driver_Surname,driver_TelNr1Number,driver_SubName,driver_TelNr4Number,driver_SubManagerEmail ,Hire_Time)
SELECT roshcon_vehicle_report_001.VehicleRegNumber, VehicleType,VehicleMake,roshcon_vehicle_drivers_v1_0.driver_FstName, roshcon_vehicle_drivers_v1_0.driver_Surname,driver_TelNr1Number,driver_SubName,driver_TelNr4Number,driver_SubManagerEmail , Hire_Time
FROM roshcon_vehicle_driver_v1_0, roshcon_vehicle_report_001, roshcon_vehicle_drivers_v1_0
WHERE roshcon_vehicle_report_001.Vehicle_Id = roshcon_vehicle_driver_v1_0.Id
AND roshcon_vehicle_drivers_v1_0.driver_Id = roshcon_vehicle_driver_v1_0.driver_FstName

mPHO

3 May 11 at 10:20 am
Can somebody please explain why this can’t fail with uniqueness violation on the url column? If another client commits a record with url = ‘http://www.xaprb.com/blog/‘ after our “SELECT … OUTER JOIN …” runs but before our INSERT does, then wouldn’t our INSERT later violate uniqueness violation by inserting the duplicate url? Just like in the Microsoft SQL example with the IF statement?

oleg

15 Jul 11 at 8:12 pm
This fails in PostgreSQL as soon as another process adds rows to the
urls table inside transactions. To reproduce:

1. Start process 1 (psql) and process 2 (psql).

2. In both processes, type “\set AUTOCOMMIT on” (this stops psql from
starting implicit transactions).

3. In process 1, type:

begin;
insert into urls(url) values (‘http://www.xaprb.com/blog/‘);

4. In process 2, type:

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;

5. Process 2 hangs waiting for process 1 to commit or roll back.

6. In process 1, type:

commit;

7. Behold error in process 2:

ERROR: duplicate key value violates unique constraint “urls_pkey”

It’s tricky to test this with two processes without a transaction, but
I suspect this can also fail without any transactions involved.

oleg

24 Aug 11 at 3:10 pm
Here’s one I used that should work in all SQL languages. I was trying to avoid using specific language stuff like EXISTS and DUPLICATE.

It’s very simple. Just insert the value(s) where 0 equals the count of the value(s) already existing.

INSERT INTO audit_type
SELECT DISTINCT ‘sample’ FROM audit_type WHERE
0 = (SELECT COUNT(audit_type_desc) FROM audit_type WHERE audit_type_desc = ‘sample’)

Jason

16 Sep 11 at 11:30 am
Jason wrote:

> Here’s one I used that should work in all SQL languages. I was trying to avoid using specific language stuff like EXISTS and DUPLICATE.
>
> It’s very simple. Just insert the value(s) where 0 equals the count of the value(s) already existing.
>
> INSERT INTO audit_type
> SELECT DISTINCT ‘sample’ FROM audit_type WHERE
> 0 = (SELECT COUNT(audit_type_desc) FROM audit_type WHERE audit_type_desc = ‘sample’)

This doesn’t work if audit_type is empty. Also, it can fail with
uniqueness violation when doing it inside transactions, like I
described one post above. Would be interesting to know if it can fail
without transactions involved too.

oleg

19 Sep 11 at 12:39 pm