SQLite UPSERT and get insert or updated rowid

Question

I have a table storing directory paths (Dirs). These directory rows are referenced by a Files table, using the directory's rowid as a foreign key. When re-indexing directories/files there is the need to check if they already exist and insert/update as necessary. After adding a directory path there will be additional insert/updates for files within the directory, which therefore need the directory's row ID. In the interest of optimization I would like to attempt to INSERT the directory and ON CONFLICT (already in the directory table) UPDATE other fields which may have changed. However, I also need the rowid of directories whether they were inserted or updated.

I first tried REPLACE, but it has the unfortunate side effect that it changes rowids, thus breaking the foreign key relationships of existing files and directories.

I was initially excited when I came across the concept of an UPSERT operation, because I thought it would do what I want. However, after much research I have come to the conclusion there is no way with current SQLite3 to get the affected rowid with an "INSERT .. ON CONFLICT UPDATE .." when an UPDATE occurs, without an additional SELECT statement to retrieve the ID by the directory name. Thus rendering this feature somewhat useless when the rowid of the insert/update is needed for additional inserts. The one improvement it can possibly perform is detecting whether an INSERT or UPDATE occurred by setting the last insert rowid to 0 with the C API function sqlite3_set_last_insert_rowid(), which will then save having to do a SELECT if an INSERT occurs, since a call to the C API sqlite3_last_insert_rowid() can be made and if it is not 0, then it is the rowid of an INSERT.

This is a pity, because SQLite obviously knows the rowid of the just updated row of an UPSERT operation, but fails to make it available. Perhaps a good feature request for future versions. Could just store it to the last_insert_rowid, since it would get set anyways in the event of an INSERT.

If anyone else has a better way to accomplish this, I'd love to hear about them.

Brandibrandice

Answer 1

Given this table:

CREATE TABLE t (
    word_id   INTEGER PRIMARY KEY,
    word_text VARCHAR NOT NULL,
    uses      INTEGER DEFAULT 1,
    UNIQUE(word_text)
);

The following upsert will always return the word_id (rowid) of the row inserted or updated, unless there's a database error. The RETURNING t.rowid part forces return of the ROWID for the row updated or created.

cursor = sqlite3.connect('x.db').cursor()
cursor.execute(
  'INSERT INTO t(word_text) VALUES ('tvalue')' \
    ' ON CONFLICT DO UPDATE ' \
      ' SET uses=uses+1' \
  ' RETURNING t.rowid;',
  ['word']
)
word_id = cur.fetchone()
if word_id:
    word_id = word_id[0]

Temperament

Answer 2

You're correct in your assessment that SQLite does not directly provide a way to retrieve the rowid of the updated row in an UPSERT operation.

There are alternative approaches you can take to achieve your goal.

1. Use an INSERT statement with an ON CONFLICT clause that will update the record when it already exists.
2. After the INSERT or UPDATE statement, execute a SELECT statement to retrieve the rowid

Leon