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: | |
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
[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