UPDATE Statement The second assignment in the following statement sets  col2  to the current (updated)  col1  value, not the original  col1  value. The result is that  col1  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 is  0  for numeric types, the empty string ( ’’ ) for string types, and the “zero”  value for date and time types. See Section 11.6, “Data Type Default Values” . If a generated column is updated explicitly, the only permitted value is  DEFAULT . For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns” . UPDATE  returns the number of rows that were actually changed. The  mysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during the  UPDATE . You can use  LIMIT  row_count  to restrict the scope of the  UPDATE . A  LIMIT  clause is a rows-matched restriction. The statement stops as soon as it has found  row_count  rows that satisfy the  WHERE  clause, whether or not they actually were changed. If an  UPDATE  statement includes an  ORDER 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 the  id  column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an  ORDER BY  clause to cause the rows with larger  id  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 use  ORDER BY  or LIMIT  with a multiple-table  UPDATE . The  table_references  clause lists the tables involved in the join. Its syntax is described in Section 13.2.10.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 in  SELECT  statements, such as  LEFT 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 the ON UPDATE  capabilities that  InnoDB  provides to cause the other tables to be modified accordingly. See Section 13.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 2704