MySQL SQL Syntax

Update statement

Syntax:

UPDATE [updateoptions] tablename SET col1=value1, col2=value2, ... [ WHERE condition ] [ ORDER BY columns ] [ LIMIT maxrecords ]

Or

UPDATE [updateoptions] table1, table2, table3 SET table1.col1=table2.col2 ... [ WHERE condition][ORDER BY columns ] [ LIMIT maxrecords ]

UPDATE changes individual fields of the table records specified by WHERE. Those fields not specified by SET remain unchanged. In value one can refer to existing fields.

For example, an UPDATE command may be of the following form:

UPDATE products SET price = price+5 WHERE productID=3

Warning: Without a WHERE condition, all data records in the table will be changed. (In the above example, the prices of all products would be increased by 5.)

. updateoptions: Here the options LOW PRIORITY and IGNORE may be given. The effect is the same as with INSERT.

. condition: This condition specifies which records are affected by the change.

. columns: With ORDER BY, you can sort the record list before making changes. This makes sense only in combination with LIMIT, for example, to change the first or last ten records (ordered according to some criterion). This possibility has existed since MySQL 4.0.

. maxrecords: With LIMIT, the maximum number of records that may be changed is specified.

Since MySQL 4.0, UPDATE commands can include more than one table. All tables included in the query must be specified after UPDATE. The only tables that are changed are those whose fields were specified by SET. The link between the tables must be set with WHERE conditions.