Obtaining rowid of an updated row in UPSERT

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Obtaining rowid of an updated row in UPSERT

Daniel Janus
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Obtaining rowid of an updated row in UPSERT

Daniel Janus
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Obtaining rowid of an updated row in UPSERT

Kees Nuyt
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users