SQL in a Nutshell

Name

UPDATE

Synopsis

The UPDATE command changes existing data in a table.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported

SQL99 Syntax and Description

UPDATE {table_name | view_name} 
SET {column_name | variable_name} = {DEFAULT | expression} [,...n]
WHERE conditions

As with the DELETE statement, an UPDATE command is seldom issued without a WHERE clause, since the statement affects every row in the entire table.

Warning

It is good practice to issue a SELECT command using the same WHERE clause before issuing the actual UPDATE statement. This checks all rows in the result set before actually performing the UPDATE. Whatever rows are returned by the SELECT are modified by the UPDATE.

Examples

A basic UPDATE statement without a WHERE clause looks like this:

UPDATE authors
SET contract = 0

Without a WHERE clause, all authors in the authors table have their contract status set to (meaning they don’t have a contract any more). Similarly, values can be adjusted mathematically with an UPDATE statement:

UPDATE titles
SET price = price * 1.1

This UPDATE statement would increase all book prices by 10%.

Adding a WHERE clause to an UPDATE statement allows records in the table to be modified selectively:

UPDATE titles 
SET    type  = 'pers_comp',
       price = (price * 1.15)
WHERE  type  = 'popular_com'

This query makes two changes to any record of the type `popular_com'. The command increases their price by 15% and alters ...