SQL Reference

Chapter 2 | Supported standards 19
UPDATE statement
Use the UPDATE statement to change records in a database table. The format of the UPDATE
statement is:
UPDATE table_name SET column_name = expr, ... [ WHERE { conditions } ]
column_name is the name of a column whose value is to be changed. Several columns can be
changed in one statement.
expr is the new value for the column.
Usually the expressions are constant values for the columns (but they can also be a subquery).
You must enclose character string values in pairs of single quotation marks ('). To include a single
quotation mark in a character string value enclosed by single quotation marks, use two single
quotation marks together (for example, 'Don''t').
Subqueries must be enclosed in parentheses.
The WHERE clause is any valid clause. It determines which records are updated.
Examples
An example of an UPDATE statement on the Employee table is:
UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001'
The UPDATE statement 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 Employee table, only one record is updated.
Here's an example using a subquery:
UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id =
'E10001'
In this case, the salary is changed to the average salary in the company for the employee having
employee ID E10001.
Note In container fields, you can UPDATE with text only, unless you prepare a parameterized
statement and stream the data from your application. To use binary data, you may simply assign
the filename by enclosing it in single quotation marks or use the PutAs() function. When
specifying the filename, the file type is deduced from the file extension:
UPDATE table_name SET (container_name) = ? AS 'filename.file
extension'
Unsupported file types will be inserted as type FILE.
When using the PutAs() function, specify the type: PutAs(col, 'type'), where the type
value is a supported file type as described in
“Retrieving the contents of a container field: CAST()
function and GetAs() function” on page 16.