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. – Daniel _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
