Blog » Insert Or Update if row exists AIR SQLite
OK leading on from the previous post, here's a solution for the INSERT or UPDATE if row exists which seems like a very common question in forums.
This sounds like it would be an easy fix, but I must be 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 to learn a little more about SQLite. The answer is quite straight forward 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 of gibberish. Basically 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 server side tables with a local database for use offline. I'm not auto incrementing table ID's rather recycling the ones form the server so I can send relevant data back. They are set to be Unique which is handy to stop duplicates ;)