Insert Or Update if row exists AIR SQLite

Following on from the previous post, here’s a solution for INSERT or UPDATE if the row exists — a fairly desirable behaviour that saves the usual SELECT if exists, then UPDATE else INSERT routine.

This sounds like it would be a straightforward fix, but I couldn’t find a single article relating to Adobe AIR/Flex insert-or-update at the time. Reading the SQLite docs helped considerably.

I was trying to SELECT column FROM Table WHERE id="ID" and branching on the result to INSERT or UPDATE respectively. The answer turns out to be quite straightforward and works similarly to MySQL’s REPLACE:

INSERT OR REPLACE INTO Table (ID, Name) VALUES (:id, :name)

The docs state: “The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one INSERT command.” In plain terms: you can specify a fallback conflict strategy. Other options include OR ABORT, OR FAIL, OR IGNORE, and OR ROLLBACK, each with different behaviour on constraint violations.

The reason I needed this was to sync server-side tables with a local database for offline use. Rather than auto-incrementing IDs locally, I was reusing the server-side IDs so data could be sent back correctly. Setting those IDs as UNIQUE ensures duplicates are handled by the conflict clause rather than requiring a manual check.