Purpose

Changes the value of columns in selected rows of a table.

Syntax

UPDATE table_name SET column_name = expression [, column_name = expression] [WHERE conditions]

where:

table_name
is the name of the table for which you want to update values.
column_name
is the name of a column, the value of which is to be changed. Multiple column values can be changed in a single statement.
expression
is the new value for the column. The expression can be a constant value or a subquery that returns a single value. Subqueries must be enclosed in parentheses.

Notes

  • A Where clause can be used to restrict which rows are updated.

Example A

This example changes every record that meets the conditions in the Where clause. In this case, the salary and exempt status are changed for all employees having the employee ID E10001. Because employee IDs are unique in the emp table, only one record is updated.

UPDATE emp SET salary=32000, exempt=1 
WHERE emp_id = 'E10001'

Example B

This example uses a subquery. In this example, the salary is changed to the average salary in the company for the employee having employee ID E10001.

UPDATE emp SET salary = (SELECT avg(salary) FROM emp) 
WHERE emp_id = 'E10001'

See also

Subqueries

Where Clause