On Sun, 19 Jan 2020 17:07:38 +0100, you wrote: >On 16.01.2020 14:02, Daniel Janus wrote: >> 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? > > Replying to myself: I ended up doing exactly this – a subsequent SELECT after > the > upsert; while this approach is working, it does feel suboptimal.
On the source code side, yes, but you have to wrap the UPSERT and subsequent SELECT in a transaction anyway, so you can be sure the relavant index and table pages are still valid in the cache. So, performancewise, it doesn't matter much. -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users