RETURNING

RETURNING appends to a supported INSERT, UPDATE, or DELETE statement and returns the final row images affected by that statement.

Description

The RETURNING clause returns affected rows in the same round trip as the data-modifying statement:

  • INSERT ... RETURNING returns inserted rows, including generated and defaulted columns.

  • UPDATE ... RETURNING returns updated rows.

  • DELETE ... RETURNING returns deleted rows.

The returned expression list can contain target-table column names, the target table or alias followed by .*, aliases, and scalar expressions. The expressions must not contain aggregates, window functions, subqueries, variables, external UDFs, or volatile functions. The old and new pseudo-namespaces are not supported.

Syntax

INSERT INTO table [(column, ...)] VALUES (...) RETURNING expression [, expression] ...
UPDATE table [AS alias] SET column = value, ... RETURNING expression [, expression] ...
DELETE FROM table WHERE ... RETURNING expression [, expression] ...

Arguments

Argument

Description

expression

A target-table column, table.*, alias, or supported scalar expression returned for each affected row.

Examples

DROP DATABASE IF EXISTS returning_demo;
CREATE DATABASE returning_demo;
USE returning_demo;

CREATE TABLE t (id BIGINT AUTO_INCREMENT PRIMARY KEY, v INT DEFAULT 7, note VARCHAR(30));
INSERT INTO t(note) VALUES ('values') RETURNING id, v, note;
INSERT INTO t(v, note) SELECT 11, 'select' RETURNING t.*, v * 2 AS twice;
UPDATE t AS x SET v = v + 10 WHERE note = 'values' RETURNING x.id, x.v, x.note;
UPDATE t SET v = 99 WHERE id = -1 RETURNING id, v;
DELETE FROM t WHERE note = 'select' RETURNING id, v, note;

DROP DATABASE returning_demo;

Constraints

RETURNING is supported only for ordinary persistent tables and the single-table DML forms shown in the syntax section. The following forms are not supported:

  • REPLACE, MERGE INTO, INSERT IGNORE, INSERT OVERWRITE, and INSERT ... ON DUPLICATE KEY UPDATE.

  • DML statements with a WITH clause or explicit PARTITION selection.

  • Priority or IGNORE modifiers, joined or multi-table UPDATE, and UPDATE ... FROM.

  • DELETE QUICK, DELETE IGNORE, DELETE USING, and multi-table DELETE.

  • Temporary, external, internal, or system tables, and non-table targets.

  • Aggregate or window expressions, subqueries, variables, external UDFs, volatile functions, and references to a non-target source in the RETURNING list.