SQLite RETURNING Clause: Get Rows Back from INSERT, UPDATE and DELETE

Ninad Pathak

October 7, 2026

Getting a new row’s ID and default values back used to take a second query, and a SELECT max(id) lookup can pick up another writer’s row. SQLite’s RETURNING clause hands those values back from the INSERT itself. In this article I’ll walk you through it, plus the limits worth knowing.

TLDR

Add RETURNING to a statement that changes data, and it returns the affected rows like a SELECT.
It needs SQLite 3.35.0 (March 2021) or later. Check yours with SELECT sqlite_version();.

RETURNING * returns every column. You can also return expressions with aliases.
For DELETE, it returns the rows as they were before they were removed.
It can’t be used as a subquery or with an aggregate such as sum().

What is the RETURNING clause in SQLite?

RETURNING makes a data-changing statement return the rows it changed. SQLite added it in version 3.35.0 (2021-03-12), and its docs say it “is designed to provide the application with the values of columns that are filled in automatically by SQLite” (SQLite RETURNING), such as an INTEGER PRIMARY KEY or a DEFAULT value.

Check that your SQLite is new enough:

sqlite> SELECT sqlite_version();
sqlite_version()
----------------
3.51.0

SQLite RETURNING syntax

INSERT INTO table_name (columns) VALUES (...) RETURNING expr [AS alias], ...;
UPDATE table_name SET ... WHERE ... RETURNING *;
DELETE FROM table_name WHERE ... RETURNING expr, ...;

How to use RETURNING

To show you how this works, I will use an orders table whose id and created_at are filled in by SQLite:

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total_cents INTEGER NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now')));

Step 1: Get the new ID from an INSERT

sqlite> INSERT INTO orders (customer, total_cents) VALUES ('ada', 1250) RETURNING id;
id
--
1

Step 2: Return every column with RETURNING *

A multi-row insert returns one row per inserted row. The created_at values are shown as <now> here because they’re the time of the test run:

sqlite> INSERT INTO orders (customer, total_cents) VALUES ('linus', 500), ('ken', 9900) RETURNING *;
id  customer  total_cents  created_at
--  --------  -----------  -------------------
2   linus     500          <now>
3   ken       9900         <now>

Step 3: Return expressions

RETURNING accepts any expression on the row’s columns, with an alias:

sqlite> INSERT INTO orders (customer, total_cents) VALUES ('barbara', 15075) RETURNING id, upper(customer) AS name, total_cents / 100.0 AS total;
id  name     total
--  -------  ------
4   BARBARA  150.75

Step 4: See the new values after an UPDATE

For UPDATE, the returned values are the ones after the change:

sqlite> UPDATE orders SET total_cents = total_cents + 100 WHERE customer IN ('ada', 'ken') RETURNING id, customer, total_cents;
id  customer  total_cents
--  --------  -----------
1   ada       1350
3   ken       10000

Step 5: See what a DELETE removed

For DELETE, you get the rows that were deleted:

sqlite> DELETE FROM orders WHERE total_cents < 1000 RETURNING id, customer;
id  customer
--  --------
2   linus

Examples

These examples cover upserts, what RETURNING can’t do, and using it from Python.

RETURNING with an upsert

“A RETURNING clause for an UPSERT reports both inserted and updated rows” (SQLite RETURNING). Here pen was updated and ink was inserted:

sqlite> CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL); INSERT INTO stock VALUES ('pen', 10);
sqlite> INSERT INTO stock VALUES ('pen', 5), ('ink', 3) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty RETURNING sku, qty;
sku  qty
---  ---
pen  15
ink  3

Aggregates aren’t allowed

RETURNING returns one row per changed row, so it can’t sum them:

sqlite> UPDATE orders SET total_cents = total_cents RETURNING sum(total_cents);
Error: in prepare, misuse of aggregate function sum()
  TE orders SET total_cents = total_cents RETURNING sum(total_cents);
                                      error here ---^

It can’t be used as a subquery

SQLite’s docs say a statement with RETURNING “cannot be used as a subquery”, unlike PostgreSQL’s data-modifying CTEs:

sqlite> SELECT * FROM (DELETE FROM orders WHERE id = 1 RETURNING id);
Error: in prepare, near "DELETE": syntax error
  SELECT * FROM (DELETE FROM orders WHERE id = 1 RETURNING id);
                 ^--- error here

Values changed by triggers aren’t returned

RETURNING shows the values as the statement wrote them, before any AFTER trigger runs. This trigger rounds the total down, but RETURNING still reports 1999:

sqlite> CREATE TRIGGER orders_round AFTER INSERT ON orders BEGIN UPDATE orders SET total_cents = (new.total_cents / 100) * 100 WHERE id = new.id; END;
sqlite> INSERT INTO orders (customer, total_cents) VALUES ('dennis', 1999) RETURNING id, total_cents;
id  total_cents
--  -----------
5   1999
sqlite> SELECT id, total_cents FROM orders WHERE customer = 'dennis';
id  total_cents
--  -----------
5   1900
sqlite> DROP TRIGGER orders_round;

RETURNING from Python

In Python’s sqlite3 module, a RETURNING statement gives you rows like a SELECT. Fetch them before you commit:

import sqlite3

con = sqlite3.connect("shop.db")
cur = con.execute(
    "INSERT INTO orders (customer, total_cents) VALUES (?, ?) RETURNING id, created_at",
    ("grace", 4200),
)
order_id, created_at = cur.fetchone()
con.commit()  # fetch the RETURNING rows before you commit
print("new order:", order_id, created_at)

rows = con.execute(
    "DELETE FROM orders WHERE total_cents < ? RETURNING id, customer", (2000,)
).fetchall()
con.commit()
print("deleted:", rows)

$ python3 returning.py
new order: 6 <now>
deleted: [(1, 'ada'), (5, 'dennis')]

Edge cases

The SQLite docs list a few more limits (SQLite RETURNING: limitations). These weren’t tested here:

The rows “appear in an arbitrary order”, and there’s no way to sort them.

RETURNING isn’t available on UPDATE and DELETE against virtual tables, such as FTS5 tables.
It “does not report any additional database changes caused by foreign key constraints or triggers”, such as rows removed by ON DELETE CASCADE.
It “may only reference the table being modified”, so in UPDATE … FROM the other tables can’t appear in it.

Conclusion

Use RETURNING whenever you need the values SQLite generated or changed, such as new IDs and defaults after an INSERT. It needs SQLite 3.35.0 or later. For totals or sorting, you still need a separate SELECT.