Filter
Top Products
UPDATE STOCKS. ORDER SET PRICE = 216000, ORDERQTY = NULL
WHERE CUSTOMER = 61 AND PRODNO = 215
Example 2:
In this example, an indicator variable is used. The data to be updated is the same as in Example 1.
In Example 2, "-1" is set for indicator variable ORDERQTY_INDICATOR, then the UPDATE statement is executed. A
value need not be specified in host variable ORDERQTY. In this case, the data to be updated becomes a null value. If
"0" or a positive value is set for the indicator variable, the data to be updated becomes the value specified in host
variable ORDERQTY.
Therefore, if the data to be updated is a null value or some other value depending on the processing for the
application program, use an indicator variable. If an indicator variable is used, only one UPDATE statement needs to
be coded. However, in processing in which the data must be specified as a null value, specify the keyword "NULL" to
simplify the coding of the UPDATE statement.
Figure: Example in which data is updated to a null value shows the execution results for the UPDATE statements in
Examples 1 and 2.
[Figure: Example in which data is updated to a null value]
3.2.3 Using default values in data to be updated
Adding data using default values is explained in 3.1.2 "Using default values in data to be added". The UPDATE
statement can also be used to update data using default values. To set a default value in data to be updated, specify
the keyword DEFAULT instead of specifying values in a set clause. If no default value is defined for the column
specified in the set clause, the data in the column is updated to a null value. However, this processing ends in an error
if a column with a NOT NULL constraint exists. The following is an example of specifying the keyword DEFAULT:
Example:
In this example, the keyword "DEFAULT" is specified. ORDERQTY is updated to the default value for
the data for PRODNO "240" in the ORDER table.
31