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

Implement UPDATE with result set

Details

    • Sprint 7 (07.04.2025)

    Description

      Add an UPDATE operation that returns a result set of the changed rows to the client.

      UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
      		 
          SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      		 
          [WHERE where_condition]
      		 
          [ORDER BY ...]
      		 
          [LIMIT row_count]
      		 
      RETURNING select_expr [, select_expr ...]

      I'm not exactly sure how the corresponding multiple-table syntax should look like, or if it is possible at all. But already having it for single-table updates would be a nice feature.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-12326 Implement multiple-table UPDATE/DELETE returning a result set

          • Major - Major loss of function.
          • Open
          duplicates

          Task - A task that needs to be done. MDEV-19093 [Feature] Issue #48 SELECT...FROM UPDATE

          • Major - Major loss of function.
          • Closed
          is duplicated by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33940 Support for UPDATE...RETURNING

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27013 COM_STMT_PREPARE returns 0 columns for INSERT ... RETURNING ...

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Task - A task that needs to be done. MDEV-30737 REPLACE...RETURNING should provide access to the old row data

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32516 Returning Insert Resultset into Temporary Table

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-3814 Implement DELETE with result set (mwl #205)

          • Major - Major loss of function.
          • Closed

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

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-12326 Implement multiple-table UPDATE/DELETE returning a result set

          • Major - Major loss of function.
          • Open

          Technical task - A technical task. MDEV-20033 sql_mode="oracle" does not support INSERT INTO ... RETURNING

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34723 NEW and OLD as row variables

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            View 7 older comments
            anel Anel Husakovic added a comment -

            Ali patch: https://github.com/alibaba/AliSQL/commit/4f246a8450bbf832178abff80e0fbcafd4068b71

            anel Anel Husakovic added a comment - Ali patch: https://github.com/alibaba/AliSQL/commit/4f246a8450bbf832178abff80e0fbcafd4068b71
            Jeff G Jeff Gullett (Inactive) added a comment -

            In addition to the proposed implementation to return new values, I would also like this to include the ability to return the old values as well. Something like:

            UPDATE table SET col1 = val1 RETURNING old.col1 AS oldCol1Val, new.col1 as newCol1Val;

            Jeff G Jeff Gullett (Inactive) added a comment - In addition to the proposed implementation to return new values, I would also like this to include the ability to return the old values as well. Something like: UPDATE table SET col1 = val1 RETURNING old.col1 AS oldCol1Val, new.col1 as newCol1Val;
            f_razzoli Federico Razzoli added a comment -

            +1 for NEW.col and OLD.col.

            But then, notice that this should also be added to INSERT ON DUPLICATE KEY UPDATE and REPLACE, both for consistency and because it's useful.

            f_razzoli Federico Razzoli added a comment - +1 for NEW.col and OLD.col. But then, notice that this should also be added to INSERT ON DUPLICATE KEY UPDATE and REPLACE, both for consistency and because it's useful.
            danblack Daniel Black added a comment -

            Note AliSQL commit above is an excellent reference, however it wasn't ever submitted as a thing to review and permission to include here. I encourage attribution even if using it as a guide.

            Both sqlite and postgres returned the modified row by default.

            Oracle Database has OLD / NEW keywords before the returning identifiers.

            As f_razzoli and Jeff G indicate the trigger like syntax of OLD.id and NEW.id would be most natural. As all of these aren't incompatible, lets take Postel Law and accommodate all syntaxes.

            danblack Daniel Black added a comment - Note AliSQL commit above is an excellent reference, however it wasn't ever submitted as a thing to review and permission to include here. I encourage attribution even if using it as a guide. Both sqlite and postgres returned the modified row by default. Oracle Database has OLD / NEW keywords before the returning identifiers. As f_razzoli and Jeff G indicate the trigger like syntax of OLD.id and NEW.id would be most natural. As all of these aren't incompatible, lets take Postel Law and accommodate all syntaxes.
            danblack Daniel Black added a comment -

            https://github.com/MariaDB/server/pull/1382 is there so I was reminded.

            danblack Daniel Black added a comment - https://github.com/MariaDB/server/pull/1382 is there so I was reminded.

            People

              danblack Daniel Black
              pprkut Heinz Wiesinger
              Votes:
              17 Vote for this issue
              Watchers:
              32 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.