Imagine the scenario when you have an SQLite database and you need to delete some of it’s content based on conditions, then list the IDs what you just deleted.

Normally it would take at least two queries: first select the ids based on the condition, store them in a list, then delete the rows with the just queried ids and continue to work with them.

SQLite has a “RETURNING” keyword, which can come in handy to reduce the queries run to just one.

For example we can utilize it as following:

DELETE FROM [file] WHERE toDelete == 1 RETURNING name

The above code will remove the rows marked “toDelete” and return the name-s of those.

Quick example using python 3:

import sqlite3

con = sqlite3.connect('./test.db')
cur = con.cursor()
cur.execute('CREATE TABLE [file] (id, name, toDelete)')
cur.execute('INSERT INTO [file] (id, name, toDelete) VALUES (1, "testFile1", 0)')
cur.execute('INSERT INTO [file] (id, name, toDelete) VALUES (2, "testFile2", 0)')
cur.execute('INSERT INTO [file] (id, name, toDelete) VALUES (3, "testFile3", 1)')
cur.execute('INSERT INTO [file] (id, name, toDelete) VALUES (4, "testFile4", 1)')   

print(cur.execute('DELETE FROM [file] WHERE toDelete == 1 RETURNING name').fetchall())

con.commit()
con.close()

It will print the following:

[('testFile3',), ('testFile4',)]