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

Implement UPDATE with result set

        Details

        • Type: Task
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Fix Version/s: 10.2
        • Component/s: None
        • Labels:
          None

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

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

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  gpfeng Guangpu Feng added a comment -

                  I have implemented a similar feature based on Perconar server 5.5.18, the syntax is:

                  SELECT
                  select_expr [, select_expr ...]
                  FROM UPDATE
                  [LOW_PRIORITY] [IGNORE]
                  [TARGET_AFFECT_ROW num]
                  tbl_name
                  SET col_name1=

                  {expr1|DEFAULT}

                  [, col_name2=

                  {expr2|DEFAULT}

                  ] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

                  for short: SELECT...FROM UPDATE...

                  I know that Oracle and PostgreSQL use the RETURNING syntax, but for MySQL(Oracle/Percona version) that requires JDBC to make some changes, which will
                  cause compatibility problems

                  Show
                  gpfeng Guangpu Feng added a comment - I have implemented a similar feature based on Perconar server 5.5.18, the syntax is: SELECT select_expr [, select_expr ...] FROM UPDATE [LOW_PRIORITY] [IGNORE] [TARGET_AFFECT_ROW num] tbl_name SET col_name1= {expr1|DEFAULT} [, col_name2= {expr2|DEFAULT} ] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] for short: SELECT...FROM UPDATE... I know that Oracle and PostgreSQL use the RETURNING syntax, but for MySQL(Oracle/Percona version) that requires JDBC to make some changes, which will cause compatibility problems
                  Hide
                  Permalink
                  gpfeng Guangpu Feng added a comment -

                  more detail about "SELECT...FROM...UPDATE", please refer to: https://bugs.launchpad.net/percona-server/+bug/1311471

                  Show
                  gpfeng Guangpu Feng added a comment - more detail about "SELECT...FROM...UPDATE", please refer to: https://bugs.launchpad.net/percona-server/+bug/1311471

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      pprkut Heinz Wiesinger
                    • Votes:
                      1 Vote for this issue
                      Watchers:
                      9 Start watching this issue

                      Dates

                      • Created:
                        Updated: