# UPDATE ... RETURNING

The `UPDATE ... RETURNING` statement returns modified rows directly as part of the updating operation. This results in highly efficient execution, especially in large datasets where scanning tables twice (once for the update and then again to fetch updated data) is expensive.

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update.md)

## 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;
```

***

Modified at: March 9, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update-returning/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update-returning/)

(An index of the documentation is available at /llms.txt)