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 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 straight forward and works exactly like MySQL REPLACE
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.
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 to stop duplicates ;)