mORMot Open Source

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

New "RETURNING" clause in the latest SQLite engine!

New "RETURNING" clause in the latest SQLite engine seems to be very helpful!

Exceprt from https://nalgeon.github.io/sqlite-3-35/:

DELETE, INSERT and UPDATE queries now can return the rows that they deleted, added, or changed respectively.

E.g., return the id of a new record:

create table users (
  id integer primary key,
  first_name text,
  last_name text
);

insert into users (first_name, last_name)
values ('Jane', 'Doe')
returning id;

Or return products with increased price:

update products set price = price * 1.10
where price <= 99.99
returning name, price as new_price;

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: New "RETURNING" clause in the latest SQLite engine!

Yes, 3.35 is a good release.
Not sure we can utilize returning in mromot ( not all Db have such feature), but another added feature is  ALTER TABLE DROP COLUMN - it solves many troubles for me (all other DB supports this). This feature also requested on forum, in this post (and some older i remember)

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

Re: New "RETURNING" clause in the latest SQLite engine!

Yes, finally! `DROP COLUMN` is great too!

Last edited by edwinsn (2021-03-13 13:17:16)

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.