sqlite3 and "ON DUPLICATE KEY UPDATE"

21 Nov 2007

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


Comment 2009-01-23 by None

I think it's easier using 'replace into' which does the same as multi-liner.


Comment 2009-12-16 by None

'REPLACE INTO' does a 'DELETE' before an 'INSERT', and this is not the behaviour intended with 'ON DUPLICATE KEY UPDATE'