Syntax
------ 
Single-table syntax:
 
UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
 [PARTITION (partition_list)]
 SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]
 
Multiple-table syntax:
 
UPDATE [LOW_PRIORITY] [IGNORE] table_references
 SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
 [WHERE where_condition]
 
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 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.
 
The PARTITION clause was introduced in MariaDB 10.0. See
Partition Pruning and Selection for details.
 
Until MariaDB 10.3.2, for the multiple-table syntax, UPDATE
updates rows in each
table named in table_references that satisfy the conditions.
In this case,
ORDER BY and LIMIT cannot be used. This restriction was
lifted in MariaDB 10.3.2 and both clauses can be used with
multiple-table updates. 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.
 
Assignments are evaluated in left-to-right order, unless the
SIMULTANEOUS_ASSIGNMENT sql_mode (available from MariaDB
10.3.5) is set, in which case the UPDATE statement evaluates
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.
 
UPDATE Statements With the Same Source and Target
 
From MariaDB 10.3.2, 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);
 
Until MariaDB 10.3.1, the following UPDATE statement would
not work:
 
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
ERROR 1093 (HY000): Table 't1' is specified twice, 
 both as a target for 'UPDATE' and as a separate source
for data
 
From MariaDB 10.3.2, the statement executes successfully:
 
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
 
SELECT * FROM t1;
 
+------+------+
| c1 | c2 |
+------+------+
| 10 | 10 |
| 21 | 20 |
+------+------+
 
Example
 
Single-table syntax:
 
UPDATE table_name SET column1 = value1, column2 = value2
WHERE id=100;
 
Multiple-table syntax:
 
UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 =
value2 WHERE tab1.id = tab2.id;
 


URL: https://mariadb.com/kb/en/update/