Obtaining rowid of an updated row in UPSERT

Dear SQLiters,

If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
already exists and needs to be updated, it doesn't seem to set lastRowid
to the rowid of that row. Observe (sqlite 3.30.1):

   > create table users (id integer primary key, firstname text, lastname text, phonenumber text);
   > create unique index idx_users_firstname_lastname on users (firstname, lastname);
   > insert into users (firstname, lastname, phonenumber) values ('John', 'Doe', '1'),
       ('Donald', 'Covfefe', '2');
   > insert into users (firstname, lastname, phonenumber) values ('John', 'Doe', '3')
       on conflict (firstname, lastname) do update set phonenumber = excluded.phonenumber;
   > select last_insert_rowid();
   2

I'd like to have obtained 1 instead, the rowid for John Doe.

I imagine the reasoning behind this is that if there was no inserted row,
then last_*insert*_rowid should remain as it was... but is there any way
to obtain this information, other than making a subsequent SELECT?

Thanks,
– Daniel
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users