Do conditional INSERT with SQL?

问题:

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

回答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.

回答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.

回答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)

回答4:

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?

回答5:

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 ...)