Do conditional INSERT with SQL?

Question

I have a database that is updated with datasets from time to time. Here it may happen that a dataset is delivered that already exists in database.

Currently I'm first doing a

SELECT FROM ... WHERE val1=... AND val2=...

to check, if a dataset with these data already exists (using the data in WHERE-statement). If this does not return any value, I'm doing my INSERT.

But this seems to be a bit complicated for me. So my question: is there some kind of conditional INSERT that adds a new dataset only in case it does not exist?

I'm using SmallSQL

***WHAT*** database system is this for? SQL is just the query language - used by many database products. SQL is **not** a database product in itself. So please let us know if you're using MySQL, Postgres, Oracle, IBM DB2, SQL Server, Firebird - or whatever else it might be... — marc_s, May 19 '13 at 16:13
Are you talking about [Merge/Upsert](http://en.wikipedia.org/wiki/Merge_(SQL))? — Randy Levy, May 19 '13 at 16:17

DougM · Accepted Answer · 2014-11-05T17:39:03.860

46

You can do that with a single statement and a subquery in nearly all relational databases.

INSERT INTO targetTable(field1) 
SELECT field1
FROM myTable
WHERE NOT(field1 IN (SELECT field1 FROM targetTable))

Certain relational databases have improved syntax for the above, since what you describe is a fairly common task. SQL Server has a MERGE syntax with all kinds of options, and MySQL has optional INSERT OR IGNORE syntax.

Edit: SmallSQL's documentation is fairly sparse as to which parts of the SQL standard it implements. It may not implement subqueries, and as such you may be unable to follow the advice above, or anywhere else, if you need to stick with SmallSQL.

edited Nov 05 '14 at 17:39

answered May 19 '13 at 16:19

DougM

2,808
17
14

4

If you are using an `INSERT... VALUES ('a,' 'b,' 'c')`, just change your statement to `INSERT... SELECT 'a', 'b', 'c' WHERE...` with no parentheses. – Noumenon Mar 16 '17 at 11:00
Note that this approach in PostgresSQL is not gauranteed to always work if there are inserts happening concurrently. – jackmott Aug 27 '19 at 18:56

Fabian Bigler · Answer 2 · 2013-05-19T16:52:05.653

25

I dont know about SmallSQL, but this works for MSSQL:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

Based on the where-condition, this updates the row if it exists, else it will insert a new one.

I hope that's what you were looking for.

edited May 19 '13 at 16:52

answered May 19 '13 at 16:14

Fabian Bigler

10,403
6
47
70

6

I don't think `IF` is part of standard SQL – Andomar May 19 '13 at 16:49
4

This is the first time I am glad that my current employer uses MSSQL databases. – Cheeku Mar 26 '14 at 12:21

score 3 · Answer 3 · answered May 25 '16 at 11:32

3

It is possible with EXISTS condition. WHERE EXISTS tests for the existence of any records in a subquery. EXISTS returns true if the subquery returns one or more records. Here is an example

UPDATE  TABLE_NAME 
SET val1=arg1 , val2=arg2
WHERE NOT EXISTS
    (SELECT FROM TABLE_NAME WHERE val1=arg1 AND val2=arg2)

answered May 25 '16 at 11:32

TMtech

1,076
10
14

Shouldn't there be something between "SELECT" AND "FROM" (other than a space)? – Lonnie Best Jul 26 '18 at 02:29
1

@LonnieBest - Probably a `select val1, val2 from table_name ...` is what was meant. – David R Tribble Nov 25 '19 at 19:42

score 0 · Answer 4 · edited May 23 '17 at 12:25

0

Usually you make the thing you don't want duplicates of unique, and allow the database itself to refuse the insert.

Otherwise, you can use INSERT INTO, see How to avoid duplicates in INSERT INTO SELECT query in SQL Server?

edited May 23 '17 at 12:25

Community

1
1

answered May 19 '13 at 16:16

ZhiHeather

162
1
10

score 0 · Answer 5 · answered May 19 '13 at 16:19

If you're looking to do an "upsert" one of the most efficient ways currently in SQL Server for single rows is this:

UPDATE myTable ...


IF @@ROWCOUNT=0
    INSERT INTO myTable ....

You can also use the MERGE syntax if you're doing this with sets of data rather than single rows.

If you want to INSERT and not UPDATE then you can just write your single INSERT statement and use WHERE NOT EXISTS (SELECT ...)

score 0 · Answer 6 · answered Dec 10 '21 at 10:28

I write this solution based on MERGE statement in SQL Server. Please note that the "source" in MERGE mustn't always be a table.

MERGE [my_target_table] t
USING (SELECT [val1]=... , [val2]=...) s
ON t.[val1]=s.[val1] AND t.[val2]=s.[val2]
WHEN MATCHED 
    THEN UPDATE set t.[val1]=s.[val1] ,
                    t.[val2]=s.[val2]
WHEN NOT MATCHED 
    THEN INSERT  ([val1], [val2])
    VALUES (s.[val1] , s.[val2]) ;

Do conditional INSERT with SQL?

6 Answers6

Linked