Insert Or Update if row exists AIR SQLite

by Alexis Hope, 16 Jan 2011

OK leading on from the previous post, here’s a solution for the INSERT or UPDATE if the row exists. Which is a fairly desirable behaviour for programmers. Saving an initial select if exists then update else insert routine.

This sounds like it would be an easy fix, but I must have been googling the wrong question because I couldn’t find one article relating to Adobe AIR/Flex insert or update. Reading the SQLite docs helped a whole bunch http://www.sqlite.org/lang_insert.html

I was trying to SELECT column FROM Table WHERE id="ID" and based upon that result ‘if else’ to INSERT or UPDATE respectively. Wanting to refactor my code and make it more efficient I set out to learn a little more about SQLite. The answer is quite straightforward and works exactly like MySQL 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.” Which is a fantastic mouthful to digest. Basically, you can specify a fallback. there are other methods available like OR ABORT which is also handy. OR FAIL,IGNORE,ROLLBACK.

The reason I needed this functionality is I’m syncing serverside tables with a local database for use offline. I’m not auto-incrementing table ID’s rather recycling the ones from the server so I can send relevant data back. They are set to be Unique which is handy for stopping duplicates ;)