2

I want to insert a record, unless it exists, then I want to update that record if its COLUMN_GENERATION value is less than that of the existing record. I'm not sure how to structure the query, or what command to use, even.

I setup my database like this:

public static final String TABLE_SEARCH = "SearchTable";
public static final String COLUMN_OWNER = "Owner";
public static final String COLUMN_SPOUSE = "Spouse";
public static final String COLUMN_CHILD = "Child";
public static final String COLUMN_GENERATION = "Generations";

private static final String DATABASE_NAME = "searches.db";
private static final int DATABASE_VERSION = 1;

// Database creation sql statement
private static final String DATABASE_CREATE = "CREATE TABLE "
        + TABLE_SEARCH + "("
        + COLUMN_OWNER + " INTEGER PRIMARY KEY, "
        + COLUMN_SPOUSE + " INTEGER DEFAULT 0, "
        + COLUMN_CHILD + " INTEGER DEFAULT 0, "
        + COLUMN_GENERATION
        + " INTEGER DEFAULT 0);";

And this is the query I"m using now, which just ignores conflicting entries:

values.put(SearchDatabaseHelper.COLUMN_OWNER, personID);
values.put(SearchDatabaseHelper.COLUMN_SPOUSE, spouseID);
values.put(SearchDatabaseHelper.COLUMN_CHILD, childID);
values.put(SearchDatabaseHelper.COLUMN_GENERATION, gens);
long insertID = database.insertWithOnConflict(SearchDatabaseHelper.TABLE_SEARCH, null, values, SQLiteDatabase.CONFLICT_IGNORE);

I tried putting a query to just search the database first, and insert or update as needed, but that was really slow and didn't actually work. I'd sure appreciate some help on this.

Wow-- closed down as a duplicate question. No real answers. I'm concerned that I didn't properly word the title to my question and so people just skimmed that and didn't read what I was asking.

Nobody seemed to notice the key phrase in the first paragraph of:

"I want to update that record if its COLUMN_GENERATION value is less than that of the existing record."

I can insert/update databases. The underlying question is, "Is it possible to make a single query that looks to see if a row exists in the database with my primary key, and if it doesn't, inserts, and if it does, then updates only if the stored value of one of the columns is higher than the inputted data?"

I really want to be able to do this all within a single SQLite call, as SQLite is so much faster than comparing and decision making outside of SQLite.

HalR
  • 11,411
  • 5
  • 48
  • 80
  • also: http://www.sqlite.org/lang_insert.html it's all in there! – Budius Apr 12 '13 at 13:49
  • 1
    Definitely not the same as INSERT IF NOT EXISTS ELSE UPDATE, because I only want to update if the new value of one column is lower than the existing value. – HalR Apr 12 '13 at 15:40
  • @Budius I've been looking at the documentation and working on this for a few days. So the diagrams and charts are sure not working for me. – HalR Apr 12 '13 at 15:46

1 Answers1

1

Read the generation of the existing record from the DB. If it exists and has a lower generation, delete it. If it did not exist or had a lower generation, insert the new one.

The various INSERT OR XXX commands only work for specific common cases; for your algorithm, you have to write it out.

Just write the commands correctly and wrap a transaction around them, and it will work, fast.

CL.
  • 173,858
  • 17
  • 217
  • 259