UPDATE statement

Use the UPDATE statement to change records in a database table. The format of the UPDATE statement is:

Copy

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.

Example

UPDATE statement on the emp table.

Copy

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.

Example

UPDATE statement on the emp table with a subquery.

Copy

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.

Important

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:

Copy

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.