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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Do conditional INSERT with SQL?

Examples related to sql

Examples related to database

Examples related to smallsql