sqlite3 and "ON DUPLICATE KEY UPDATE"

MySQL has a great SQL extension "INSERT ... ON DUPLICATE KEY UPDATE" (doco here). As you might guess it either inserts a new row, but if it exists already, you can specify an update. It's particular great for doing frequency counts:

INSERT INTO atable SET name='foo', count=4 ON DUPLICATE KEY UPDATE count=count+4

sqlite3 doesn't have this functionality, but it's easy to fake with a little programming. I'm going to use python as an example, but I'm sure it applies to other languages.

import sqlite3

# setup code here

try:
   cursor.execute("INSERT INTO atable SET name='foo', count = 4")
except sqlite3.IntegrityError, m:
    cursor.execute("UPDATE atable SET count = count + 4")

# more

With Sqlite3 you'll need to make sure a unique index exists (in this example, for the 'name' field).