DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) UPDATE

Info Catalog (mysql.info.gz) TRUNCATE (mysql.info.gz) Data Manipulation
 
 13.1.10 `UPDATE' Syntax
 -----------------------
 
 Single-table syntax:
      UPDATE [LOW_PRIORITY] [IGNORE] TBL_NAME
          SET COL_NAME1=EXPR1 [, COL_NAME2=EXPR2 ...]
          [WHERE WHERE_DEFINITION]
          [ORDER BY ...]
          [LIMIT ROW_COUNT]
 
 Multiple-table syntax:
 
      UPDATE [LOW_PRIORITY] [IGNORE] TBL_NAME [, TBL_NAME ...]
          SET COL_NAME1=EXPR1 [, COL_NAME2=EXPR2 ...]
          [WHERE WHERE_DEFINITION]
 
 The `UPDATE' statement updates columns in existing table rows with new
 values.  The `SET' clause indicates which columns to modify and the
 values they should be given.  The `WHERE' clause, if given, specifies
 which rows should be updated.  Otherwise, all rows are updated. If the
 `ORDER BY' clause is specified, the rows will be updated in the order
 that is specified. The `LIMIT' clause places a limit on the number of
 rows that can be updated.
 
 The `UPDATE' statement supports the following modifiers:
 
    * If you specify the `LOW_PRIORITY' keyword, execution of the
      `UPDATE' is delayed until no other clients are reading from the
      table.
 
    * If you specify the `IGNORE' keyword, the update statement will 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 closet valid values instead.
 
 
 If you access a column from TBL_NAME in an expression, `UPDATE' uses
 the current value of the column.  For example, the following statement
 sets the `age' column to one more than its current value:
 
      mysql> UPDATE persondata SET age=age+1;
 
 `UPDATE' assignments are evaluated from left to right.  For example, the
 following statement doubles the `age' column, then increments it:
 
      mysql> UPDATE persondata SET age=age*2, age=age+1;
 
 If you set a column to the value it currently has, MySQL notices this
 and doesn't update it.
 
 If you update a column that has been declared `NOT NULL' by setting to
 `NULL', the column is set to the default value appropriate for the
 column type and the warning count is incremented.  The default value is
 `0' for numeric types, the empty string (`''') for string types, and
 the "zero" value for date and time types.
 
 `UPDATE' returns the number of rows that were actually changed.  In
 MySQL 3.22 or later, 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'.
 
 Starting from MySQL 3.23, you can use `LIMIT ROW_COUNT' to restrict the
 scope of the `UPDATE'. A `LIMIT' clause works as follows:
 
    * Before MySQL 4.0.13, `LIMIT' is a rows-affected restriction.  The
      statement stops as soon as it has changed ROW_COUNT rows that
      satisfy the `WHERE' clause.
 
    * From 4.0.13 on, `LIMIT' 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.  `ORDER BY' can be used
 from MySQL 4.0.0.
 
 Starting with MySQL 4.0.4, you can also perform `UPDATE' operations
 that cover multiple tables:
 
      UPDATE items,month SET items.price=month.price
      WHERE items.id=month.id;
 
 The example shows an inner join using the comma operator, but
 multiple-table `UPDATE' statements can use any type of join allowed in
 `SELECT' statements, such as `LEFT JOIN'.
 
 Note: You cannot use `ORDER BY' or `LIMIT' with multiple-table `UPDATE'.
 
 Before MySQL 4.0.18, you need the `UPDATE' privilege for all tables
 used in a multiple-table `UPDATE', even if they were not updated.  As
 of MySQL 4.0.18, you need only the `SELECT' privilege for any columns
 that are read but not modified.
 
 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 will fail and
 roll 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.
 
 Currently, you cannot update a table and select from the same table in a
 subquery.
 
Info Catalog (mysql.info.gz) TRUNCATE (mysql.info.gz) Data Manipulation
automatically generated byinfo2html