# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.