Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8307

Allow DELETE FROM RETURNING to be used with INSERT INTO

Details

    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;

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-8347 Allow DELETE RETURNING to be used inside stored procedures

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-10422 INSERT RETURNING

          • Major - Major loss of function.
          • Closed

          Activity

            dveeden Daniël van Eeden added a comment -

            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)

            dveeden Daniël van Eeden added a comment - 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)

            People

              Unassigned Unassigned
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              8 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.