Changing Values in a Table (UPDATE)

The UPDATE statement modifies rows in a table. Use this statement to change

• All data items in all rows of a table

• All data items in one row of a table

• Some data items in some rows of a table

Standard SQL has two versions of the UPDATE statement. Both are easy to use; they differ in how you specify which row or rows to change. In one form, you use a WHERE clause (see 5.1.3) to restrict the changes to the desired row or rows. In the other form, you substitute WHERE CURRENT OF cursor name (use the name of the cursor) for the WHERE clause. This restricts the update to the current row (that is, to the row you have just retrieved using the cursor).

Note: If you do not put a WHERE clause on the UPDATE statement, the change applies to all rows.

In both versions of the UPDATE statement, you first name the table in which you want to change values. Next, you list the changes you want to make. Each change is a column name followed by an equal sign ( =) and the new value that column is to take.

For the special case in which you wish to update a value and then immediately retrieve it (for example, if a bank adds $10 to an account balance and then reports the new

balance), use the RETRIEVE INTO clause. The RETRIEVE INTO clause on the UPDATE statement is available only in the embedded SQL (ESQL) interface to RDMS from COBOL.

Note: When a table’s primary key is updated, it is treated as a DELETE statement followed by an INSERT statement. If a duplicate key is encountered, RDMS rolls back to the last commit point unless the COMMANDLOOKS recovery option is invoked. For more information about the DELETE and INSERT statements, see 6.1 and 6.2. Examples

The following example changes the asking price for house number H101 in table HOUSES to $150,000:

UPDATE houses

SET(price = 150000) WHERE hno = 'H101'

With one UPDATE statement, the next example changes the asking price for two houses:

Views

7.1.

Creating a View (CREATE VIEW)

You use the CREATE VIEW statement so that you can access data in a different way. A view is a virtual table that can be defined in terms of one or more underlying tables or views, or tables and views. Like tables, views can be created and dropped at any time. A view is, in effect, a window into the underlying table or tables.

To use the CREATE VIEW statement, specify the following:

• A view name

• Column names

If you do not list column names, the default column names for the view are the same as the column names resulting from the select list. Column names must be unique within the view. If you do list column names, you must use as many as are required to satisfy the select list.

• A query specification to retrieve data from the underlying tables and views to populate the view you are creating

• An optional WITH CHECK OPTION clause

RDMS ensures that UPDATE and INSERT operations against the view satisfy the WHERE clause of the definition of the view.

• An optional ACCESS CONTROL clause for security

The user who creates a view is the owner of that view. When a view is created within the context of the CREATE SCHEMA statement, the owner is the authorization-id of the CREATE SCHEMA statement. The owner is the only person who can grant and revoke privileges for that view.

The owner of a view inherits access privileges from the base table or tables on which the view depends. For example, if only one base table exists and the view owner has SELECT and INSERT access privileges to that table, the owner has those privileges to the view as well. The one exception to this rule is that if the view cannot be updated, the only privilege that can be granted is SELECT.

Example

The following example defines a view using table HOUSES: CREATE VIEW s1.v1_houses

(v1_hno, v1_loc, v1_price)

AS SELECT hno, location, price FROM s1.houses WHERE price < 150000

WITH CHECK OPTION

Views can be made up of views themselves or any combination of views and tables. Every level of table or view join, select list, and WHERE clause is another level of filtration to produce the data picture you want. This enables you to nest query specifications to retrieve precisely the information you want.

7.1.1.

Qualifier Name and View Name

You can use an explicit qualifier name to associate a view with a schema, or you can make the association by default.

If the CREATE VIEW statement is made within the context of a CREATE SCHEMA statement, the view name is implicitly associated with the name of the schema. If the view is not in the CREATE SCHEMA statement and you use a USE DEFAULT QUALIFIER statement, the default qualifier you specify qualifies the view.

Barring any of these conditions, the schema name is determined by the system default qualifier. That is, it is either RDMS or the current user-id, depending on the setting of the RDMS-DEFAULT-QUALIFIER configuration attribute (a UREP dynamic system

reconfiguration parameter).

Views are defined on logical, not physical, tables and views. Therefore, any attempt to specify a version with the name of the view being defined or any views or tables referenced within its definition are rejected.

Views are associated with a particular data set at run time by either explicitly specifying a version for the view or by using the default version whenever the view is referenced in a manipulative statement. Upon resolution of the view, the underlying tables use the version currently associated with the view.

7.1.2.

Column Names

You can provide a list of the column names you want to use in the view definition. Here are some of the rules to keep in mind:

• Names must be valid names and unique within the view column list.

• The number of columns specified must be equal to the number of columns appearing in the select list of the SELECT clause.

• The order of the columns in the view column list must parallel columns derived from the select list of the SELECT clause.

• Columns can be derived from a built-in function, an arithmetic expression, or a constant.

• The view column list is mandatory when the select list contains an arithmetic expression, a function, a constant, or duplicate column names.

Examples

The following example associates columns HNO1 and LOC1 with HNO and LOC from table S1.HOUSES, and HNO2 and LOC2 with HNO and LOC from table S2.HOUSES. Since the tables in the FROM clause have identical column names, a view column list must be included; otherwise, RDMS returns an error.

CREATE VIEW v1 (hno1,loc1,hno2,loc2) AS SELECT a.hno,a.loc,b.hno,b.loc FROM s1.houses a, s2.houses b

In the next example, no view column list is given, and all columns are specified in the SELECT statement. The columns for V2 have the same names as the columns in S1.HOUSES.

CREATE VIEW v2

7.1.3.

View Query Specification

The query specification in the CREATE VIEW statement provides an additional resource for focusing on the underlying tables and views. Section 4 gives you details about formulating a query specification, but be aware of the following restrictions when you write the query specification in the CREATE VIEW statement.

• All columns specified in the SELECT clause, Boolean expression, and GROUP BY or HAVING clause must belong to one of the tables or views in the FROM clause.

• Tables and views referenced must already exist.

• The user of the view being defined must at least have select privileges on all the tables referenced in the statement.

• The UNION[ALL] set operator is not allowed.

Also, you cannot put an aggregate operation in the query specification if you specify a view in the FROM clause that has an aggregate operation in its query specification. Aggregate operations are

• DISTINCT keyword • GROUP BY clause • HAVING clause • Built-in functions − SUM − AVG − MAX − MIN − COUNT

7.1.4.

Updating Views

You can use some views in an UPDATE, INSERT, or DELETE statement to alter the contents of the underlying table. A view must meet the following conditions to be used in an UPDATE statement:

• The view must be derived from a single underlying base table. The FROM clause of the view must list no more than one table or view.

• The select list cannot contain arithmetic expressions, constants, or functions; it can contain only column names or an asterisk.

• The view cannot contain a DISTINCT specification in the SELECT clause. $ The view cannot contain a GROUP BY clause.

• The view’s list of column names can rename any one column in the base table no more than once.

• Any view listed in the view’s FROM clause must adhere to all of these rules.

• The view cannot contain a subquery or the UNION[ALL] set operator. A view must meet the following conditions to be used in an INSERT or DELETE statement:

• The view must meet all the aforementioned conditions for the UPDATE statement.

• The view must select all the columns in the underlying base table. In other words, there must be an exact, one-to-one correspondence between the columns in the view and the columns in the underlying base table.

Note: If certain aspects of a view’s underlying table are altered, that view and any other views dependent on that table are dropped. Keep this in mind particularly when using the ALTER TABLE statement.

7.1.5.

View WITH CHECK OPTION Clause

The WITH CHECK OPTION clause on the view definition directs RDMS to verify that any updated or inserted values satisfy the view WHERE clause. This enables you to enforce domain constraints that logically exist on columns in a base table. With the WITH CHECK OPTION clause, the WHERE clause is checked by any view that builds on this view.

Examples

Suppose that table ORDERS contains the column MONTH that is defined to contain a 2-digit decimal value in the range 1 through 12. Column MONTH is declared as a 2-digit decimal number with no digits after the decimal point. You can enforce this constraint by building the following view:

CREATE VIEW verified_orders AS SELECT * FROM orders WHERE month BETWEEN 1 AND 12

WITH CHECK OPTION

By using view VERIFIED_ORDERS instead of table ORDERS, all values of the column MONTH are in the range 1 to 12.

Suppose table ORDERS contained another column, PRICE, that is defined to contain a 7-digit decimal value with 2 digits after the decimal point. Creating the following view illustrates how CHECK OPTION works:

CREATE VIEW verified_orders1 AS SELECT * FROM verified_orders WHERE price > 1000.00

The view VERIFIED_ORDERS1 is built on top of view VERIFIED_ORDERS, on which WITH CHECK OPTION is specified. If PRICE has a value smaller than 1000.00, view VERIFIED_ORDERS1 does not reject it because this view has no WITH CHECK OPTION clause specified. However, if MONTH has a value not between 1 and 12, view