MySQL :: MySQL 8.4 Reference Manual :: 15.2.17 UPDATE Statement (original) (raw)

15.2.17 UPDATE Statement

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 theUPDATE. See Section 15.2.20, “WITH (Common Table Expressions)”.

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, theUPDATE 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 keyword DEFAULT to set a column explicitly to its default value. TheWHERE clause, if given, specifies the conditions that identify which rows to update. With noWHERE clause, all rows are updated. If theORDER BY clause is specified, the rows are updated in the order that is specified. TheLIMIT 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 tablereferences 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 aPARTITION clause 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 wherecondition or not.

Note

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

For more information and examples, seeSection 26.5, “Partition Selection”.

wherecondition is an expression that evaluates to true for each row to be updated. For expression syntax, see Section 11.5, “Expressions”.

tablereferences and_wherecondition_ are specified as described in Section 15.2.13, “SELECT Statement”.

You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. You need only theSELECT privilege for any columns that are read but not modified.

The UPDATE statement supports the following modifiers:

UPDATE IGNORE statements, including those having an ORDER BY clause, are flagged as unsafe for statement-based replication. (This is because the order in which the rows are updated determines which rows are ignored.) Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439) SeeSection 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement setscol1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement setscol2 to the current (updated)col1 value, not the originalcol1 value. The result is thatcol1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

If you set a column to the value it currently has, MySQL notices this and does not update it.

If you update a column that has been declared NOT NULL by setting to NULL, an error occurs if strict SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type and the warning count is incremented. The implicit default value is0 for numeric types, the empty string ('') for string types, and the“zero” value for date and time types. SeeSection 13.6, “Data Type Default Values”.

If a generated column is updated explicitly, the only permitted value is DEFAULT. For information about generated columns, seeSection 15.1.20.8, “CREATE TABLE and Generated Columns”.

UPDATE returns the number of rows that were actually changed. Themysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during theUPDATE.

You can use LIMIT_`rowcount`_ to restrict the scope of the UPDATE. ALIMIT clause is a rows-matched restriction. The statement stops as soon as it has found_rowcount_ rows that satisfy theWHERE clause, whether or not they actually were changed.

If an UPDATE statement includes anORDER BY clause, the rows are updated in the order specified by the clause. This can be useful in certain situations that might otherwise result in an error. Suppose that a table t contains a column id that has a unique index. The following statement could fail with a duplicate-key error, depending on the order in which rows are updated:

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in theid column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add anORDER BY clause to cause the rows with largerid values to be updated before those with smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

You can also perform UPDATE operations covering multiple tables. However, you cannot useORDER BY or LIMIT with a multiple-table UPDATE. The_tablereferences_ clause lists the tables involved in the join. Its syntax is described inSection 15.2.13.2, “JOIN Clause”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted inSELECT statements, such asLEFT JOIN.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on theON UPDATE capabilities thatInnoDB provides to cause the other tables to be modified accordingly. SeeSection 15.1.20.5, “FOREIGN KEY Constraints”.

You cannot update a table and select directly from the same table in a subquery. You can work around this by using a multi-table update in which one of the tables is derived from the table that you actually wish to update, and referring to the derived table using an alias. Suppose you wish to update a table nameditems which is defined using the statement shown here:

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

To reduce the retail price of any items for which the markup is 30% or greater and of which you have fewer than one hundred in stock, you might try to use an UPDATE statement such as the one following, which uses a subquery in theWHERE clause. As shown here, this statement does not work:

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

Instead, you can employ a multi-table update in which the subquery is moved into the list of tables to be updated, using an alias to reference it in the outermost WHERE clause, like this:

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

Because the optimizer tries by default to merge the derived tablediscounted into the outermost query block, this works only if you force materialization of the derived table. You can do this by setting thederived_merge flag of theoptimizer_switch system variable to off before running the update, or by using the NO_MERGE optimizer hint, as shown here:

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

The advantage of using the optimizer hint in such a case is that it applies only within the query block where it is used, so that it is not necessary to change the value ofoptimizer_switch again after executing theUPDATE.

Another possibility is to rewrite the subquery so that it does not use IN or EXISTS, like this:

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

In this case, the subquery is materialized by default rather than merged, so it is not necessary to disable merging of the derived table.