Filter
Top Products
Chapter 3 Modifying Data
This chapter describes how to use the INSERT statement, UPDATE statement (searched) and DELETE statement
(searched) to modify data.
This chapter contains the following sections:
3.1 Adding Data to a Data Base
3.2 Updating Data in a Data Base
3.3 Deleting Data from a Data Base
3.1 Adding data to a Data Base
Use the INSERT statement to add data to a table. The following is an example of the INSERT statement:
Example:
In this example, the following data is added to the ORDER table: "78" for CUSTOMER, "400" for
PRODNO, "125,000" for PRICE, and "50" for ORDERQTY.
This chapter explains the following operations that can be performed by the INSERT statement:
· Specifying a null value in data to be added
· Using a default value in data to be added
· Specifying a current date and time in data to be added
· Adding data from another table
3.1.1 Specifying a null value in added data
To specify a null value in data to be added to a table, specify the keyword NULL or use an indicator variable instead of
specifying a value in the insert value list. The following are examples of adding a null value in data:
Example 1:
In this example, the keyword NULL is specified. The following data is added to the ORDER table: "78"
for CUSTOMER, "400" for PRODNO, and "50" for ORDERQTY. PRICE is specified as a null value.
INSERT INTO STOCKS. ORDER (CUSTOMER, PRODNO, PRICE, ORDERQTY)
VALUES (78, 400, NULL, 50)
Example 2:
In this example, an indicator variable is used. The data to be added is the same as in Example 1.
INSERT INTO STOCKS. ORDER (CUSTOMER, PRODNO, PRICE, ORDERQTY)
VALUES (78, 400, :PRICE INDICATOR :PRICE_INDICATOR, 50)
In Example 2, "-1" is set as indicator variable PRICE_INDICATOR before the INSERT statement is executed. A value
need not be set in host variable PRICE. In this case, the added data is a null value. However, if "0" or a positive value
is specified as the indicator variable, then the data to be added becomes the value specified in host variable PRICE.
21