SQLite conditional insert or replace

Question

I'm trying to insert or update a record into a sqlite database, and only update the value if the new value is greater than the old.

The schema is:

 CREATE table IF NOT EXISTS SearchTable
   (Owner INTEGER PRIMARY KEY, Generations INTEGER DEFAULT 0)

I've tried commands like this:

INSERT OR REPLACE INTO SearchTable(Owner, Generations)
 VALUES (?, MAX((SELECT Generations FROM SearchTable WHERE Owner = ?), ?))

but it gives me a null value for the Generations.

I could use some help.

score 7 · Accepted Answer · answered Mar 21 '13 at 05:21

7

Try

INSERT OR REPLACE INTO SearchTable(Owner, Generations)
SELECT ?, MAX(Generations) FROM SearchTable WHERE Owner = ?

answered Mar 21 '13 at 05:21

peterm

Thanks peterm. I ended up using: INSERT OR REPLACE INTO SearchTable(Owner, Generations) SELECT ?, MIN(?, COALESCE(Generations, 999)) FROM SearchTable WHERE Owner = ? – HalR Mar 21 '13 at 05:33

1 Answers1