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.

peterm · Accepted Answer · 2013-03-21 05:21:21Z

7

Try

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

answered Mar 21, 2013 at 5:21

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HalR Over a year ago

Thanks peterm. I ended up using: INSERT OR REPLACE INTO SearchTable(Owner, Generations) SELECT ?, MIN(?, COALESCE(Generations, 999)) FROM SearchTable WHERE Owner = ?

Collectives™ on Stack Overflow

SQLite conditional insert or replace

1 Answer 1

1 Comment

Hot Network Questions