Databases with SQLite

Returning

A very handy addition to SQLite beginning with version 3.35.0 (2021-03-12), is the RETURNING clause.

Used with either insert, update, or delete, it returns the value of the row affected.


It is NOT available for virtual tables as of yet.
The returning values may not be in the same order as the initial command.

An example explains it better:

update books set titleid=443 where titleid=442 returning titleid;
titleid
-------
443

insert into books(titleid,authorid,title,isbn,pubyear)
values(20001,30003,"Steal This Book",1234567898976,2029)
returning *;
id    titleid  authorid  title            pubyear    ISBN
----  -------  --------  ---------------  -------    -------------
1019  20001    30003     Steal This Book  2029       1234567898976

This saves you having to do another 'select' query to verify the change, which is useful.

The returning clause is not standard SQL, but takes its birth from PostgreSQL.

See here for more.