MySQL Update operation

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] table_references    SET col_name1=expr1 [, col_name2=expr2 ...]    [WHERE Where_definition]

The update syntax can update the columns in the original table row with the new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used to limit the number of rows that can be updated, given a limit value.

If you access a column through tbl_name in an expression, update uses the current value in the column. For example, the following statement sets the age column to one more than the current value:

UPDATE persondata SET age=age+1;

The update assignment is evaluated from left to right. For example, the following statement doubles the age column and then adds:

UPDATE persondata SET age=age*2, age=age+1;

If you set a column to the value it currently contains, MySQL will notice it, but it will not update.

Some fields of the update table are NULL

Update person set number=null,name=null;

If you update a column that is already defined as NOT NULL to NULL, the column is set to the default value corresponding to the column type, and the number of warnings is accumulated. For numeric types, the default value is 0, and for string types, the default is an empty string ('), and for a date and time type, the default value is "zero".

Update operations for multiple tables

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

The above example shows an internal union using the comma operator, but the Multiple-table UPDATE statement can use any type of union allowed in the SELECT statement, such as a left JOIN, but you cannot put the order By or limit is used in conjunction with Multiple-table update.

Transfer from Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#update

MySQL Update operation