SQLite RETURNING Clause

Summary: in this tutorial, you will learn how to use the SQLite RETURNING clause to return data from the INSERT, UPDATE, and DELETE statements.

Introduction to the SQLite RETURNING clause

Starting from SQLite 3.35.0, the INSERT, UPDATE, and DELETE have an optional RETURNING clause that returns the row that is inserted, updated, or deleted.

Here’s the syntax of the RETURNING clause when used with the INSERT statement:

INSERT INTO table_name(column_list)
VALUES(value_list)
RETURNING 
    expression1 AS column_alias1,  
    expression2 AS column_alias2,
    ...; Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, place the RETURNING clause after the INSERT statement.
• expression1, expression2, and so on are the columns of the inserted row. They can be expressions that involve the columns. Additionally, you can use column aliases to return a row with the new column name

If you want to return all columns of the inserted row without forming expressions or aliases, you can use the asterisk (*) shortcut:

INSERT INTO table_name(column_list)
VALUES(value_list)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Similarly, you can use the RETURNING clause with the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 =value2, 
WHERE condition
RETURNING 
    expression1 AS column_alias1,  
    expression2 AS column_alias2,
    ...; Code language: SQL (Structured Query Language) (sql)

In this syntax, the RETURNING clause returns the modified row.

Likewise, the RETURNING clause in a delete statement returns the deleted row:

DELETE FROM table_name
WHERE condition
RETURNING 
    expression1 AS column_alias1,  
    expression2 AS column_alias2,
    ...; Code language: SQL (Structured Query Language) (sql)

It’s important to note that for the INSERT and UPDATE statements, the RETURNING clause returns the rows after SQLite has applied the change. For the DELETE statements, the RETURNING clause returns the row before the delete.

In practice, you use the RETURNING clause to get the inserted, updated, and deleted row without having to issue a separate query.

SQLite RETURNING clause examples

Let’s take examples of using the RETURNING clause. We’ll create a new table called books for the demonstration:

CREATE TABLE books(
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    isbn TEXT NOT NULL,
    release_date DATE
);Code language: SQL (Structured Query Language) (sql)

1) Returning inserted rows

First, insert a new row into the books table and return the inserted row:

INSERT INTO books(title, isbn, release_date)
VALUES('The Catcher in the Rye', '9780316769488', '1951-07-16')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                   isbn           release_date
--  ----------------------  -------------  ------------
1   The Catcher in the Rye  9780316769488  1951-07-16Code language: SQL (Structured Query Language) (sql)

In this example, the RETURNING clause returns all columns of the inserted row because of the asterisk.

Second, insert a new row into the books table and return the inserted book id:

INSERT INTO books(title, isbn, release_date)
VALUES ('The Great Gatsby', '9780743273565', '1925-04-10')
RETURNING id;Code language: SQL (Structured Query Language) (sql)

Output:

id
--
2Code language: SQL (Structured Query Language) (sql)

In this example, the RETURN clause returns the id of the inserted book.

Third, insert a new row into the books table and return the book id and release year:

INSERT INTO books(title, isbn, release_date)
VALUES ('The Great Gatsby', '9780743273565', '1925-04-10')
RETURNING 
   id AS book_id, 
   strftime('%Y', release_date) AS year;Code language: SQL (Structured Query Language) (sql)

Output:

book_id  year
-------  ----
3        1925Code language: SQL (Structured Query Language) (sql)

Fourth, insert two rows and return the inserted rows:

INSERT INTO books (title, isbn, release_date) 
VALUES
    ('Pride and Prejudice', '9780141439518', '1813-01-28'),
    ('The Lord of the Rings', '9780618640157', '1954-07-29')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                  isbn           release_date
--  ---------------------  -------------  ------------
4   Pride and Prejudice    9780141439518  1813-01-28
5   The Lord of the Rings  9780618640157  1954-07-29Code language: SQL (Structured Query Language) (sql)

2) Returning updated rows

First, use the UPDATE statement uses the RETURNING clause to update the ISBN of the book with id 1 and return the updated row:

UPDATE books
SET isbn = '0141439512'
WHERE id = 1
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                   isbn        release_date
--  ----------------------  ----------  ------------
1   The Catcher in the Rye  0141439512  1951-07-16Code language: SQL (Structured Query Language) (sql)

Second, use the UPDATE statement uses the RETURNING clause to change the book title to uppercase and return the updated rows:

UPDATE books
SET title = UPPER(title)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                   isbn           release_date
--  ----------------------  -------------  ------------
1   THE CATCHER IN THE RYE  0141439512     1951-07-16
2   THE GREAT GATSBY        9780743273565  1925-04-10
3   THE GREAT GATSBY        9780743273565  1925-04-10
4   PRIDE AND PREJUDICE     9780141439518  1813-01-28
5   THE LORD OF THE RINGS   9780618640157  1954-07-29Code language: SQL (Structured Query Language) (sql)

3) Returning deleted rows

First, delete a book with id 1 and return the deleted row:

DELETE FROM books
WHERE id = 1
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                   isbn        release_date
--  ----------------------  ----------  ------------
1   THE CATCHER IN THE RYE  0141439512  1951-07-16Code language: SQL (Structured Query Language) (sql)

Second, delete all rows from the books table and return multiple deleted rows:

DELETE FROM books
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                  isbn           release_date
--  ---------------------  -------------  ------------
2   THE GREAT GATSBY       9780743273565  1925-04-10
3   THE GREAT GATSBY       9780743273565  1925-04-10
4   PRIDE AND PREJUDICE    9780141439518  1813-01-28
5   THE LORD OF THE RINGS  9780618640157  1954-07-29Code language: SQL (Structured Query Language) (sql)

SQLite RETURNING clause restrictions

Here are some restrictions when using the RETURNING clause:

• The RETURNING cannot be used as a subquery even though it returns a result set. It can only return data to the application.
• If the RETURNING clause returns multiple rows, the order of rows is unspecified.
• The RETURNING clause returns the original rows that do not reflect changes made by triggers.
• The RETURNING clause may not contain top-level aggregate functions or window functions.
• The RETURNING clause can only reference the table being modified. It cannot reference the table that drives the modification such as in the UPDATE FROM statement.

Summary

• Use the RETURNING clause in the INSERT, UPDATE, DELETE statement to return inserted, updated, and deleted rows.