Return Rows From an INSERT, UPDATE, or DELETE

Get the affected rows back in the same statement — using `RETURNING` in PostgreSQL, MariaDB, and SQLite, or `OUTPUT` in SQL Server.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Apply a price increase and get the updated row back

The UPDATE reduces Widget's stock from 100 to 90. With RETURNING product_id, product_name, stock, the engine returns the post-update row values in the same response, so the application does not need a follow-up SELECT. SQL Server's OUTPUT INSERTED.col syntax achieves the same result using the INSERTED pseudo-table, which holds the new row state after the update. In an UPDATE, DELETED holds the old values and INSERTED holds the new values, making it possible to return both old and new state in a single OUTPUT clause.

MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE inventory (
  product_id INT,
  product_name VARCHAR(50),
  stock INT
);

INSERT INTO
  inventory
VALUES
  (1, 'Widget', 100),
  (2, 'Gadget', 50),
  (3, 'Doohickey', 75);

Validated query

SQL

UPDATE inventory
SET
  stock = stock - 10
WHERE
  product_id = 1 RETURNING product_id,
  product_name,
  stock;

Expected result

product_id	product_name	stock
1	Widget	90

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE inventory (
  product_id INT,
  product_name VARCHAR(50),
  stock INT
);

INSERT INTO
  inventory
VALUES
  (1, 'Widget', 100),
  (2, 'Gadget', 50),
  (3, 'Doohickey', 75);

Validated query

SQL

UPDATE inventory
SET
  stock = stock - 10 OUTPUT INSERTED.product_id,
  INSERTED.product_name,
  INSERTED.stock
WHERE
  product_id = 1;

Expected result

product_id	product_name	stock
1	Widget	90

PostgreSQL, MariaDB, and SQLite use `RETURNING`. SQL Server uses `OUTPUT INSERTED.*`. MySQL has no equivalent.

Useful when

Where this command helps.

retrieving a database-generated primary key after an INSERT without a second round trip
confirming the new column value immediately after an UPDATE

Explanation

What the command is doing.

Normally a write statement (INSERT, UPDATE, DELETE) reports only a row count. The RETURNING clause (PostgreSQL, MariaDB, SQLite) or OUTPUT clause (SQL Server) makes the engine return the affected rows as a result set in the same round trip. This eliminates the follow-up SELECT that would otherwise be needed to retrieve a generated ID or verify the new value after an update. RETURNING * returns all columns; you can list specific columns or expressions instead. SQL Server's OUTPUT clause uses INSERTED and DELETED pseudo-tables to reference the new and old row state — INSERTED for INSERT and UPDATE, DELETED for DELETE, and both together for UPDATE when you need to compare before and after. MySQL has no equivalent; the generated key from an INSERT must be retrieved with a separate LAST_INSERT_ID() call.

Return Rows From an INSERT, UPDATE, or DELETE

Apply a price increase and get the updated row back

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.