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

Implement UPDATE with result set

    XMLWordPrintable

Details

    • Sprint 7 (07.04.2025), Q4/2025 Server Development

    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.

      Other databases:

      • PostgreSQL: uses RETURNING, returns new values
      • Oracle: ditto
      • SQL Server: uses OUTPUT and inserted. or deleted. prefixes for columns to return new/old values.

      Our options:
      Supposedly, we want to be Oracle and PostgreSQL compatible while being able to return old column values too.

      UPDATE ... RETURNING NEW.col as new_col, OLD.col as old_col;
      		 
      UPDATE ... RETURNING col as new_col, VALUE(col) as old_col;
      		 
      UPDATE ... RETURNING col as new_col, OLD_VALUE(col) as old_col;

      First option. How to extend it for multi-update, should it be NEW.t1.col or t1.NEW.col ? What is UPDATE ... RETURNING is inside a trigger, how to parse NEW/OLD there?

      Second option. VALUE already exists, not meaningful inside ODKU, so free to use. But not intuitively readable. Particularly in INSERT ... ON DUPLICATE KEY UPDATE col=VALUES(col) RETURNING VALUES(col).

      Third option. New keyword, new function, only meaningful inside UPDATE RETURNING. But fairly readable without consulting the manual.

      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 DELETE/REPLACE/INSERT ... RETURNING ...

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

          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 in a trigger as row variables

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

          Activity

            People

              rucha174 Rucha Deodhar
              pprkut Heinz Wiesinger
              Votes:
              18 Vote for this issue
              Watchers:
              35 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.