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

Answer 1

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.

Answer 2

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.