Топ-100
 
RDBMS Type: MySQL
Topic

Syntax:
UPDATE is a DML statement that modifies rows in a table.

An UPDATE statement can start with a WITH clause to define common table
expressions accessible within the UPDATE. See
https://dev.mysql.com/doc/refman/8.0/en/with.html.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

value:
{expr | DEFAULT}

assignment:
col_name = value

assignment_list:
assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

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.

For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. Each matching row is
updated once, even if it matches the conditions multiple times. For
multiple-table syntax, ORDER BY and LIMIT cannot be used.

For partitioned tables, both the single-single and multiple-table forms
of this statement support the use of a PARTITION option as part of a
table reference. This option takes a list of one or more partitions or
subpartitions (or both). Only the partitions (or subpartitions) listed
are checked for matches, and a row that is not in any of these
partitions or subpartitions is not updated, whether it satisfies the
where_condition or not.

*Note*:

Unlike the case when using PARTITION with an INSERT or REPLACE
statement, an otherwise valid UPDATE ... PARTITION statement is
considered successful even if no rows in the listed partitions (or
subpartitions) match the where_condition.

For more information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.

where_condition is an expression that evaluates to true for each row to
be updated. For expression syntax, see
https://dev.mysql.com/doc/refman/8.0/en/expressions.html.

table_references and where_condition are specified as described in
https://dev.mysql.com/doc/refman/8.0/en/select.html.

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.

The UPDATE statement supports the following modifiers:

o With the LOW_PRIORITY modifier, 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 (such as MyISAM,
MEMORY, and MERGE).

o With the IGNORE modifier, the update statement does not abort even if
errors occur during the update. Rows for which duplicate-key
conflicts occur on a unique key value are not updated. Rows updated
to values that would cause data conversion errors are updated to the
closest valid values instead. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#ignore-effect-o
n-execution.

URL: https://dev.mysql.com/doc/refman/8.0/en/update.html

Example