(mysql.info) update

Info Catalog

(mysql.info) truncate

(mysql.info) data-manipulation

 
 13.2.10 `UPDATE' Syntax
 -----------------------
 
 Single-table syntax:
 
      UPDATE [LOW_PRIORITY] [IGNORE] TBL_NAME
          SET COL_NAME1=EXPR1 [, COL_NAME2=EXPR2 ...]
          [WHERE WHERE_CONDITION]
          [ORDER BY ...]
          [LIMIT ROW_COUNT]
 
 Multiple-table syntax:
 
      UPDATE [LOW_PRIORITY] [IGNORE] TABLE_REFERENCES
          SET COL_NAME1=EXPR1 [, COL_NAME2=EXPR2 ...]
          [WHERE WHERE_CONDITION]
 
 For the single-table syntax, the `UPDATE' statement updates columns of
 existing rows in `tbl_name' with new values. The `SET' clause indicates
 which columns to modify and the values they should be given. 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. In this case,
 `ORDER BY' and `LIMIT' cannot be used.
 
 WHERE_CONDITION is an expression that evaluates to true for each row to
 be updated. It is specified as described in  select.
 
 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.
 
    * 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 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:
 
      UPDATE persondata SET age=age+1;
 
 `UPDATE' assignments are evaluated from left to right. For example, the
 following statement doubles the `age' column, and then increments it:
 
      UPDATE persondata SET age=age*2, age=age+1;
 
 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', the column is set to the default value appropriate for the data
 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. 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.
 
 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  join. 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 allowed
 in `SELECT' statements, such as `LEFT JOIN'.
 
 You need the `UPDATE' privilege only for columns referenced in a
 multiple-table `UPDATE' that are actually updated. 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 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  innodb-foreign-key-constraints.
 
 Currently, you cannot update a table and select from the same table in
 a subquery.
 

Info Catalog

(mysql.info) truncate

(mysql.info) data-manipulation