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.

Created Apr 19, 2026 Last updated Apr 19, 2026 4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Delete a row and return the removed values

The DELETE removes Widget from the inventory table and returns the removed row in the same response. PostgreSQL, MariaDB, and SQLite use RETURNING product_id, product_name, stock. SQL Server's OUTPUT DELETED.col syntax achieves the same result using the DELETED pseudo-table, which holds the row state before deletion.

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

DELETE FROM inventory
WHERE
  product_id = 1 RETURNING product_id,
  product_name,
  stock;

Expected result

product_id	product_name	stock
1	Widget	100

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

DELETE FROM inventory OUTPUT DELETED.product_id,
DELETED.product_name,
DELETED.stock
WHERE
  product_id = 1;

Expected result

product_id	product_name	stock
1	Widget	100

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

Delete a row and return the removed values

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.