UPDATE statement page 1
UPDATE statement changes existing data in a table.
With a single UPDATE statement, arbitrary number of columns can change their values. However a column cannot be changed in the same UPDATE statement more than once. All rows of a table will be affected if a WHERE clause is eliminated from an UPDATE statement.
If the column is defined to allow NULL values, NULL can be specified explicitly. Moreover, existing value can be changed by default value (DEFAULT keyword is used) for a given column.
An expression may refer to current values in a table to be changed. For example, we may decrease prices by 10 percent on all the laptops with the following statement:
The values of one column are allowed to assign to another column. For example, it is required to change HD less than 10 Gb in laptops. New HD capacity must be one-half of available RAM. The solution may be as follows:
Clearly, data types of the hd and ram columns need to be compatible. The CAST expression (item 5.9) may be used for conversion.
If the data needs to be changed depending on contents of some column, we can use the CASE expression (item 5.10). Say, if it is needed to setup HD with capacity of 20 Gb in laptops with RAM less than 128 Mb and HD with capacity of 40 Gb in remaining laptops, we can write the query:
Use of subquery is also allowed to define the column value. For example, we need to complete all laptops with most fast available processor. Then we write:
Some words about autoincrementable columns ought to be said. If the code column in the Laptop table is defined as IDENTITY(1,1), then the following statement
Clearly, another row with the value code=5 must not be in the table.