Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

UPDATE ... RETURNING

In addition to performance gains, the RETURNING clause simplifies application logic by ensuring that the exact set of modified rows is returned. This is especially useful when the conditions for updating involve fields that are also being modified, making it difficult to re-identify the affected rows in a separate query.

Syntax

UPDATE <table_name>
  SET <assignment_list>
    [WHERE <where_condition>]
    [LIMIT <row_count>]
[RETURNING *| <column_name> [[AS] alias] [, <column_name> [[AS] alias]...]];

In the above syntax:

RETURNING * - returns all columns of each updated row.
RETURNING <column_name> [, <column_name> ...] - returns only the listed columns from each updated row.
RETURNING <column_name> [AS] alias - assigns an alias to a returned column. Aliases appear in the result set metadata and can be used by client applications.

For information on other arguments in the syntax refer UPDATE

Remarks

This statement works for both rowstore and columnstore tables.

It can be used to update only a single table, joins/subselects are not supported.

Aliased fields in expression are supported.

Only fields are supported for RETURNING clause, which includes * or fields or a combination of both.

Stored procedures and CTEs are not supported.

Operations on the fields in the RETURNING clause are not supported. For example, the following clause is not supported: RETURNING t.a+1, t.b*2, t.b+t.c.

UPDATE…RETURNING with the LIMIT clause across partitions is not supported.

Examples

Example 1: The following example differentiates between using UPDATE with and without the RETURNING clause.

You want to give a 10% raise to all employees whose salaries are between $50,000 and $70,000. You also want to return their IDs and new salaries for reporting purposes.

Using UPDATE…RETURNING:

UPDATE employees
  SET salary = salary * 1.10
    WHERE salary BETWEEN 50000 AND 70000
RETURNING id, salary AS new_salary;

Without the RETURNING clause:

BEGIN;
  SELECT id, salary * 1.10 AS new_salary         
    FROM employees             
      WHERE salary BETWEEN 50000 AND 70000 FOR UPDATE;
 
  UPDATE employees          
    SET salary = salary * 1.10             
      WHERE salary BETWEEN 50000 AND 70000;
COMMIT;

Example 2: The following example updates a JSON field and returns the updated documents for further processing. This pattern lets you atomically update and retrieve JSON data without a separate SELECT.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  profile JSON);

INSERT INTO customers
  VALUES(1, '{"name":"Alice","status":"active"}'),     
        (2, '{"name":"Bob","status":"active"}');

-- Mark inactive customers and return the updated profiles

UPDATE customers     
  SET profile = JSON_SET_STRING(profile, 'status', 'inactive')        
    WHERE profile::$status = 'active'
RETURNING id, profile;

Example 3: Update with a LIMIT to get the number of rows from a table.

CREATE TABLE t (
  a INT,
  b INT, SHARD KEY(b));

INSERT INTO t
  VALUES (1, 1), (2, 1), (3, 1), (4, 1);

UPDATE t
  SET a = 20
    WHERE b = 1
      LIMIT 2
RETURNING *;

UPDATE t
  SET a = b+10
    WHERE b = 1  
      LIMIT 5
RETURNING *;

Example 4: You can use aliases in the RETURNING clause to match your application’s expected column names.

UPDATE accounts    
  SET balance = balance - 100         
    WHERE account_id = 42
RETURNING account_id AS id, balance AS new_balance;

UPDATE ... RETURNING

On this page

Remarks

Examples

Was this article helpful?

On this page

Was this article helpful?