Bug #83139 Resultset for insert, update and delete
Submitted: 25 Sep 2016 11:32 Modified: 5 Oct 2016 7:07
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: returning

[25 Sep 2016 11:32] Daniël van Eeden
Description:
With the CTE support of MySQL 8.0 (optimizer labs release) it would be nice to support returning a resultset for update and delete statements.

For this the RETURNING statement should be implemented.

A use case would be to fill a logging table with values from a delete or update statement without having to use triggers (e.g. only do this for batch operations)

How to repeat:
In PostgreSQL:

test=# create table t1 (id serial, name varchar(100));
CREATE TABLE
test=# insert into t1(name) values ('foo'),('bar');
INSERT 0 2
test=# with deleted_names as (delete from t1 where id = 2 returning name)
test-# select name from deleted_names;
 name 
------
 bar
(1 row)

This is also implemented in MariaDB:
https://mariadb.com/kb/en/mariadb/delete/
[25 Sep 2016 11:37] Daniël van Eeden
Oracle has a very similar RETURNING INTO clause:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#BABHDGIG

This could also be used to return the values for fields for a insert query for which a values was not specified in the query. This would return the applied column defaults.
[5 Oct 2016 7:07] MySQL Verification Team
Hello Daniël,

Thank you for the feature request!

Thanks,
Umesh
[15 Sep 2020 7:54] Lukas Eder
PostgreSQL (as well as Oracle PL/SQL (but not SQL), and Firebird, MariaDB) support RETURNING clauses at the end of all DML statements, e.g.:

INSERT INTO t (a, b) VALUES (1, 2) RETURNING c, d;
UPDATE t SET a = 1 RETURNING b;
DELETE t RETURNING b;

SQL Server has a similar OUTPUT clause on all DML statements.

Db2 implements the standard SQL data change delta table feature (which I would recommend following):

SELECT * FROM FINAL TABLE (INSERT INTO t (a, b) VALUES (1, 2));
SELECT * FROM NEW TABLE (UPDATE t SET a = 1);
SELECT * FROM OLD TABLE (DELETE t);

Notice that standard SQL allows for accessing data before the DML operation (OLD), after the DML operation (NEW), as well as after firing triggers (FINAL). SQL Server's OUTPUT also allows for this to some extent. The RETURNING syntax returns "FINAL" semantics for INSERT/UPDATE, and "OLD" semantics for DELETE