[MDEV-8307] Allow DELETE FROM RETURNING to be used with INSERT INTO Created: 2015-06-11  Updated: 2018-02-01

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Delete
Fix Version/s: None

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 8
Labels: None

Issue Links:
Relates
relates to MDEV-8347 Allow DELETE RETURNING to be used ins... Open
relates to MDEV-10422 INSERT RETURNING Closed

 Description   

First discussed here: https://lists.launchpad.net/maria-discuss/msg02647.html

Just regarding the RETURNING feature introduced into MariaDB 10.0.5 https://mariadb.com/kb/en/mariadb/delete/

It would be useful, similar to the OUTPUT clause in MSSQL, if we could INSERT the data sent back from a returning clause in a single statement. Something like…

CREATE TABLE deleted_ids
 
(
 
                Id INT NOT NULL PRIMARY KEY
 
);
 
 
INSERT INTO deleted_ids
 
DELETE FROM t1
 
WHERE id < 999
 
RETURNING id;



 Comments   
Comment by Daniël van Eeden [ 2015-10-17 ]

This is how it looks like in PostgreSQL. Note the use of CTE (MDEV-8308)

postgres=# SELECT * FROM t1;
 
 id | myarray 
----+---------
 
  1 | {1,2,4}
 
(1 row)
 
 
 
postgres=# SELECT * FROM t2;
 
 id | myarray 
----+---------
 
(0 rows)
 
 
 
postgres=# WITH del_t1 AS (DELETE FROM t1 RETURNING *)                                                                           
INSERT INTO t2 SELECT * FROM del_t1;
 
INSERT 0 1
 
postgres=# SELECT * FROM t1;                                                                                                      
id | myarray 
----+---------
 
(0 rows)
 
 
 
postgres=# SELECT * FROM t2;                                                                                                      
id | myarray 
----+---------
 
  1 | {1,2,4}
 
(1 row)

Generated at Thu Feb 08 07:26:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.