# UPDATE

## Syntax

{% hint style="info" %}
For the CTE[^1] syntax, available from MariaDB 12.3, see [here](#cte-syntax).
{% endhint %}

Single-table syntax:

```sql
UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
  [PARTITION (partition_list)]
  [FOR PORTION OF period FROM expr1 TO expr2]
  SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]
  RETURNING OLD_VALUE(val) AS old [, val as new]
```

{% hint style="info" %}
The `RETURNING` clause is available from MariaDB 13.0.

`RETURNING` works only for single tables.

It uses the [`OLD_VALUE()`](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/old-value) function to return a value *val* as the old value (before the `UPDATE`), and optionally `val` as the new value (after the `UPDATE`).

See [this example](#single-table-with-returning-clause) for how it is used.
{% endhint %}

Multiple-table syntax:

```sql
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
    [WHERE where_condition]
```

### CTE Syntax

{% hint style="info" %}
This syntax is available from MariaDB 12.3.
{% endhint %}

```sql
WITH [RECURSIVE] table_reference [(columns_list)] AS  (
  SELECT ...
)
UPDATE non_cte_table expression
```

* `non_cte_table` is a table not defined by a CTE (common table expression).
* `expression` is a `WHERE` clause.
* Supporting CTEs with `UPDATE` is an extension of the SQL standard, similar to how MySQL does it.
* With `UPDATE`, CTEs are read-only, like other derived tables – you cannot update columns from tables in the CTE expression.
* For use cases, see the [CTE examples](#single-table).

## Description

For the single-table syntax, the `UPDATE` statement updates columns of existing rows in the named table with new values. The`SET` clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword`DEFAULT` to set a column explicitly to its default value. The`WHERE` clause, if given, specifies the conditions that identify which rows to update. With no `WHERE` clause, all rows are updated. If the [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) clause is specified, the rows are\
updated in the order that is specified. The [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) clause places a limit on the number of rows that can be updated.

{% tabs %}
{% tab title="Current" %}
Both clauses can be used with multiple-table updates.
{% endtab %}

{% tab title="< 10.3" %}
Both clauses can be used with multiple-table updates. For the multiple-table syntax, `UPDATE` updates rows in each table named in `table_references` that satisfy the conditions. In this case, [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) and [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) could not be used.
{% endtab %}
{% endtabs %}

An `UPDATE` can also reference tables which are located in different databases; see [Identifier Qualifiers](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-qualifiers) for the syntax.

`where_condition` is an expression that evaluates to true for each row to be updated.

`table_references` and `where_condition` are as specified as described in [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select).

For single-table updates, assignments are evaluated in left-to-right order, while for multi-table updates, there is no guarantee of a particular order. If the `SIMULTANEOUS_ASSIGNMENT` [sql\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) is set, `UPDATE` statements evaluate all assignments simultaneously.

You need the `UPDATE` privilege only for columns referenced in an `UPDATE` that are actually updated. You need only the [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) privilege for any columns that are read but not modified. See [GRANT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant).

The `UPDATE` statement supports the following modifiers:

* If you use the `LOW_PRIORITY` keyword, execution of the `UPDATE` is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). See [HIGH\_PRIORITY and LOW\_PRIORITY clauses](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/high_priority-and-low_priority) for details.
* If you use the `IGNORE` keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.

### PARTITION

See [Partition Pruning and Selection](https://mariadb.com/docs/server/server-usage/partitioning-tables/partition-pruning-and-selection) for details.

### FOR PORTION OF

See [Application Time Periods - Updating by Portion](https://mariadb.com/docs/server/sql-structure/temporal-tables/application-time-periods#updating-by-portion).

### UPDATE Statements With the Same Source and Target

`UPDATE` statements may have the same source and target. For example, given the following table:

```sql
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (10,10), (20,20);

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);

SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|   10 |   10 |
|   21 |   20 |
+------+------+
```

## Examples

### Single-Table

```sql
UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=100;
```

### Single-Table With RETURNING Clause

{% hint style="info" %}
The RETURNING clause is available from MariaDB 13.0.
{% endhint %}

```sql
UPDATE t SET a=a+1 RETURNING OLD_VALUE(a) AS old, a as new;
+------+------+
| old  | new  |
+------+------+
|    1 |    2 |
+------+------+
```

### Multi-Table

```sql
UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 = value2 WHERE tab1.id = tab2.id;
```

### CTE Single-Table

```sql
WITH cte1 AS (SELECT * FROM t1 WHERE c < 5), 
     cte2 AS (SELECT * FROM t2 WHERE b < 5) 
     UPDATE t3 SET t3.a = (SELECT a FROM cte1 WHERE b IN (SELECT b FROM cte2));
```

### CTE Multi-Table

```sql
WITH cte1 AS (SELECT * FROM t1 WHERE c < 5),
     cte2 AS (SELECT * FROM t2 WHERE b < 5)
     UPDATE t3, cte1 SET t3.a = cte1.a WHERE cte1.b IN (SELECT b FROM cte2);
```

```sql
WITH cte1 AS (SELECT * FROM t1 WHERE c < 5),
     cte2 AS (SELECT * FROM t2 WHERE b < 5)
     UPDATE t3, cte1, cte2 SET t3.a = cte1.a WHERE cte1.b = cte2.b;
```

## See Also

* [How IGNORE works](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/ignore)
* [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select)
* [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by)
* [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit)
* [Identifier Qualifiers](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-qualifiers)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}

[^1]: CTE (Common Table Expression): A temporary, named result set that exists only for the duration of a single `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement, used to make complex queries more readable.