How to do a insert or replace when the primary key will not be used

Question

I have a sqlite database on a multi device android system that requires UUID (yes it requires them), however I do not set the UUID as the primary key, instead the UUID is just a column and the primary key is just an INTEGER on each device (that is not transferred) but instead auto generated on the local android database

I am doing an Update (if failed) insert routine based on if the UUID exists -which is quite slow and eventually I want to convert this into a bulk system with the SQLITESTATEMENT

however in order for me to get to that point I need to make the INSERT OR REPLACE (or REPLACE INTO) work with my data set - From what I understand it only works based on if the primary key already exists - since my primary key is not what I actually care about how do I make the Replace Into work based of a different column? Or perhaps I can do bulk inserts a different way - that does not involve replace intos - but can still handle inserts or updates -

any help is appreciated

Some of the items I have been using for references How do I UPDATE a row in a table or INSERT it if it doesn't exist?

http://www.buzzingandroid.com/2013/01/sqlite-insert-or-replace-through-contentprovider/

Improve INSERT-per-second performance of SQLite?

How do I use prepared statements in SQlite in Android?

http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html#execute() Android SQLite database: slow insertion

Answer 1

You can use INSERT OR REPLACE even if the UUID is not the primary key as long as you make the UUID field UNIQUE;

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  uuid UUID UNIQUE NOT NULL
);

Any insert that violates the UNIQUE constraint replaces the existing row (but generates a new primary key for the new row)

A very simple SQLfiddle to test with.