You use UPDATE to update the column values of rows in a table.
The primary key value in a partitioned table may not be modified.
The special literals (see "Special literals"), as well as the time functions CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP in the UPDATE statement (and in default values) are evaluated once, and the values calculated are valid for all updates.
If you want to update a row in the specified table, you must own the table or have the UPDATE privilege for each of the columns to be updated. Furthermore, the transaction mode of the current transaction must be READ WRITE.
If integrity constraints have been defined for the table or the columns involved, these are checked after the update operation. If an integrity constraint has been violated, the updates are canceled and an appropriate SQLSTATE set.
UPDATE table [[AS] correlation_name ]
SET colspec = column_value [, colspec = column_value ]...
[WHERE { search_condition | CURRENT OF cursor }]
colspec ::= { column | column ( posno ) | column ( min..max ) }
column_value ::= { expression | <{ value | NULL},...> | DEFAULT | NULL }
table
Name of the table containing the rows you want to update. The table can be a base table or an updatable view.
correlation_name
Table name used in the statement as a new name for the table table.
The correlation_name must be used to qualify the column name in every column specification that references the table table if the column name is not unambiguous.
The new name must be unique, i.e. correlation_name can only occur once in a table specification of this statement.
You must give a table a new name if the columns in the table cannot be identified otherwise uniquely.
In addition, you may give a table a new name in order to formulate an expression so that it is more easily understood or to abbreviate long names.
column
Name of an atomic column whose contents you want to update. The column must be part of the table. You can only specify a column once in an UPDATE statement.
column(pos_no)
Element of a column containing the value you want to update.
The multiple column must be part of the table. If you specify several elements in a multiple column, the range of elements specified must be contiguous. Each element can only be specified once.
pos_no is an unsigned integer >= 1.
column(min..max)
Range of column elements in a multiple column that are to be assigned values. The multiple column must be part of the table. If you specify several elements in a multiple column, the range of elements specified must be contiguous.
Each element can only be specified once.
min and max are unsigned integers >= 1; max must be >= min.
expression
Expression whose value is to be assigned to the preceding atomic column. The value of the expression must be compatible with the data type of the column (see section "Entering values in table columns").
If expression is a host variable, you can also specify a vector. If you do so, the column must be a multiple column and the number of elements in the vector must be the same as the number of column elements.
The following restrictions apply to expression:
Neither the underlying base table for table nor a view of this base table can be included in the FROM clause of a subquery in expression.
Aggregate functions (AVG, MAX, MIN, SUM, COUNT) are not permitted.
<{ value , NULL},...>
Aggregate to be assigned to a multiple column.
The number of occurrences must be the same as the number of column elements. The data type of value must be compatible with the data type of the target column (see section "Entering values in table columns").
DEFAULT
Only for atomic columns.
If a default value is defined for a particular column, this value is entered in that column. Otherwise, the column is assigned the NULL value.
NULL
The preceding column is assigned the NULL value.
WHERE clause
The WHERE clause indicates the rows to be updated.
WHERE clause omitted:
All the rows in the table are updated.
search_condition
Condition that the rows to be updated must satisfy. A row is only updated if it satisfies the specified condition.
The following restrictions apply to search_condition:
Column specifications in search_condition outside of subqueries can only reference the specified table.
Neither the underlying base table for table nor a view of this base table can be included in the FROM clause of a subquery included in search_condition.
If no row satisfies the search condition, no row is updated and an SQLSTATE is set that can be handled with WHENEVER NOT FOUND.
CURRENT OF cursor
Name of the cursor used to determine the row to be updated. table must be the table specified in the first FROM clause of the cursor description.
The cursor must satisfy the following conditions:
The cursor must reference table.
The cursor must be updatable.
When the UPDATE statement is executed, the cursor must be open and positioned on a row in the table with FETCH. In addition, the FETCH statement must be executed in the same transaction as the UPDATE statement.
UPDATE updates the row indicated by cursor.
UPDATE is not permitted if block mode is activated for cursor (see section "PREFETCH pragma").
If cursor was declared with the FOR UPDATE clause and column specifications, only the columns specified in that clause can be updated.
The UPDATE statement does not influence the position of the cursor. If you want to update the next row in the derived table, you must position the cursor on this row with FETCH.
Updating the values in a multiple column
In the case of a multiple column, you can update values for individual column elements or for ranges of elements.
An element of a multiple column is identified by its position number in the multiple column.
A range of elements in a multiple column is identified by the position numbers of the first and last element in the range.
UPDATE and integrity constraints
By specifying integrity constraints when you define the base table, you can restrict the possible contents of table. After UPDATE statement has been executed, the contents of table must satisfy the defined integrity constraints.
UPDATE and updatable view
If CHECK OPTION is specified in the definition of an updatable view, only rows that satisfy the query expression in the view definition can be inserted in the view.
UPDATE and transaction management
UPDATE initiates a transaction outside routines if no transaction is open. If you define an isolation level for concurrent transactions, you can control how the UPDATE statement affects these transactions (see section "SET TRANSACTION - Define transaction attributes").
If an error occurs during an UPDATE statement, any updates that have already been performed are canceled.
Examples
Increase the minimum stock level of all items to 20.
UPDATE items SET min_stock = 20 WHERE min_stock < 20
Update the minimum stock level using a cursor:
DECLARE cur_items CURSOR FOR SELECT min_stock FROM items WHERE min_stock < 20 FOR UPDATE OPEN cur_items
Update the rows involved with a series of FETCH and UPDATE statements.
FETCH cur_items INTO :MIN_STOCK UPDATE items SET min_stock = 20 WHERE CURRENT OF cur_items
Use the cursor CUR_VAT to select all services for which no VAT is calculated. Update the rows involved with a series of FETCH and UPDATE statements.
DECLARE cur_vat CURSOR WITH HOLD FOR
SELECT service_num, service_text, vat
FROM service WHERE vat=0.00
FOR UPDATE
FETCH NEXT cur_vat
INTO :SERVICE_NUM, :SERVICE_TEXT INDICATOR :IND_SERVICE_TEXT :VAT INDICATOR :IND_VAT
UPDATE service SET vat=0.15 WHERE CURRENT OF cur_vat
...
Update the intensity of the individual color components for the color orange in the COLOR_TAB table. The column RGB for the color intensity is a multiple column:
UPDATE color_tab SET rgb(1..3) = <0.8, 0.4, 0> WHERE color_name = 'orange'
See also
DELETE, INSERT, MERGE