UPDATE

Complete UPDATE statement guide for MariaDB. Complete syntax reference with WHERE conditions, JOIN operations, CTEs, and multi-table updates for production use.

Syntax

For the CTE syntax, available from MariaDB 12.3, see here.

Single-table syntax:

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]

The RETURNING clause is available from MariaDB 13.0.

RETURNING works only for single tables.

It uses the 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 for how it is used.

Multiple-table syntax:

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

CTE Syntax

This syntax is available from MariaDB 12.3.

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.

Description

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. TheSET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keywordDEFAULT to set a column explicitly to its default value. TheWHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

Current

Both clauses can be used with multiple-table updates.

< 10.3

An UPDATE can also reference tables which are located in different databases; see 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.

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 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 privilege for any columns that are read but not modified. See 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 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 for details.

FOR PORTION OF

See 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:

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

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

Single-Table With RETURNING Clause

The RETURNING clause is available from MariaDB 13.0.

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

Multi-Table

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

CTE Single-Table

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

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);

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

• SELECT

• ORDER BY

• LIMIT

• Identifier Qualifiers

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}