SQLite: update a record if exists, otherwise insert it?

Question

I tried INSERT OR REPLACE INTO, but it doesn't preserve the row id when it replaces the record to update it. Another option is do it in two steps: INSERT OR IGNORE INTO then UPDATE, but I would prefer a one step solution. So I am wondering if SQLite has something like the MERGE keyword or other simple solutions?

Answer 1

No, SQLite doesn't support MERGE or upsert.

You can use your two-step solution, but what you probably really want is for the ROWID to be a first-class column in your table. If you declare a column as INTEGER PRIMARY KEY, it will be an alias for the ROWID. Then INSERT OR REPLACE will work fine.