Insert Or Update if row exists AIR SQLite

Posted by Alexis on 16 January 2011 | 0 Comments

Tags: ,

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


Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments