The UPDATE Statement

The UPDATE statement modifies the contents of the existing records. To use it, name the table needing to be updated, provide a SET clause that lists one or more column value assignments, and optionally specify a WHERE clause that identifies which records to update. The Update statement uses the following general syntax;

UPDATE table_name SET column=expression(s) WHERE condition [options];

For example, to update the Country table by doubling the Population column for every country, the following statement would be used;

mysql> UPDATE Country 

          -> SET Population = Population * 2;

Query OK, 232 rows affected, 1 warning (#.## sec)

Rows matched: 239 Changed: 232 Warnings: 1

Note: The above statement returns a warning and completes the update for only most of the rows, but not all. This is because some of the updated numbers exceed the number of characters allowed for that column.

The effects of column assignments made by an UPDATE are subject to column type constraints, just as they are for an INSERT or REPLACE. By default, if an attempt to update a column to a value that doesn't match the column definition is made, MySQL converts or truncates the value. If strict SQL mode is enabled, the server will be more restrictive about allowing invalid values.It's possible for an update to have no effect. This can occur under the following conditions:

•  When it matches no records for updating; due to an empty table or if no records match the WHERE clause.
• When it does not actually change any column values (i.e. the value given is the same as the existing value).With respect to handling of records with unique key values, UPDATE is similar to REPLACE in some ways,but the two aren't equivalent:
• UPDATE does nothing if there is no existing record in the table that contains the specified key values. REPLACE does not require an existing record with the key values and adds one if none exists.
• UPDATE can be used to change some columns in an existing record while leaving others unchanged.

REPLACE entirely discards the existing record. To achieve the effect of leaving some columns

unchanged with REPLACE, the new record must specify the same values in those columns that the existing record has.

UPDATE by default makes no guarantee about the order in which rows are updated. This can sometimes result in problems. Suppose that the people table contains two rows, where id is a primary key:

mysql> SELECT * FROM people;

+--------------------+

| id | name | age |

+--------------------+

| 2 | Victor | 21    |

| 3 | Susan | 15   |

+--------------------+