UPDATE on empty table doesn't return an error

Databases
1

What’s the best strategy for managing a combined “UPDATE and INSERT if doesn’t exist” operation in SQLite?

I’m trying a strategy of an UPDATE, checking for an error and then INSERTING that row if I got an error.

Unfortunately doing an UPDATE on a row that doesn’t exist in an empty table seems to not generate an error…

I’ve tried calling this using both SQLExecute and SQLSelect…

Ideas or best practices???

Thanks, Joe

2

Joe,

Have you looked at using the “INSERT OR REPLACE” keyword in SQLite? Here is a good discussion on it (Insert or Replace) and some caveats about it’s usage.

3

An Update as you specified won’t cause an error, because it did exactly what you told it to do.

Since the table was empty… nothing matched the WHERE clause, so no update occurred… but that is not an error in this case.
An error would have been it having FAILED to do something it should have been able to do.

Like Jim said, if you are looking to ADD data to an empty table, then INSERT is the correct command

4

Jim

Thanks for the helpful answer and link. Looks like this isn’t as simple a task as I’d hoped. I’ll go back to doing a SELECT and then an INSERT or UPDATE based on whether I get zero or one records back.

5 
dim where_clause as string="WHERE DeviceID = '184553161' AND IPAddress = '67.167.218.205' AND IPPort = '3392'"
dim rs as recordset
rs=db.SQLSELECT("Select 8 from connections "+where_clause) /// AND YES... that is an "8" there ... on purpose
if db.ERROR then 
   <do something with the error>
else
   if rs.RECORDCOUNT=0 then // no existing record
      db.SQLEXECUTE("INSERT into connections [yada yada] "+where_clause)
  else
     db.SQLEXECUTE("UPDATE connections SET [yada yada] "+where_clause)
  end if
end if

needs more error checking of course, but that the basic idea.

and why “8” you ask? instead of “*”?
All we care about is if the record EXISTS, NOT wasting time/resources creating and populating a data vector

6

HI Dave

That’s basically what I did originally but thought there must be a more optimized way for SQlite to handle such a common Update/Insert operation instead of me handling all of the logic myself with three separate calls.

Thanks for confirming that I wasn’t too far off.

Cheers, Joe

7

INSERT OR REPLACE into table …

http://sqlite.org/lang_insert.html

8

[quote=182970:@Norman Palardy]INSERT OR REPLACE into table …

http://sqlite.org/lang_insert.html[/quote]

Yes, as long as you understand the caveats, especially with an auto-increment primary key. If you don’t specify the primary key value, you will end up with an insert and not and update. This link has the details: