ALLBASE/SQL Reference Manual (36216-90216)

584 Chapter12

SQL Statements S - Z

UPDATE

or your program is running.

• The output device cannot be overwritten with a SELECT or FETCH statement unless you

use the INSERT or UPDATE statement with the overwrite option.

• LONG columns cannot be used as follows:

• In a WHERE clause.

• In a type II INSERT statement.

• Remotely through ALLBASE/NET.

• As hash or B-tree index key columns.

• In a GROUP BY, ORDER BY, DISTINCT, or UNION clause.

• In an expression.

• In a subquery.

• In aggregate functions (AVG, SUM, MIN, MAX).

• As columns to which integrity constraints are assigned.

• With the DEFAULT option of the CREATE or ALTER TABLE statements.

• If no input device is speciﬁed, only output information of LONG columns is reset.

• If no output device is speciﬁed, only value is reset.

Authorization

You can update a table if you have UPDATE authority for the entire table, UPDATE

authority for all of the columns speciﬁed in the SET clause, OWNER authority for the

table, or DBA authority.

To update using a view, the authority needed as described below depends on whether you

own the view:

• If you own the view, you need UPDATE or OWNER authority for the base table, or

UPDATE authority for each column of the base table to be updated as speciﬁed in the

SET clause, or DBA authority.

• If you do not own the view, you must have UPDATE authority for the view, or UPDATE

authority for each column of the view speciﬁed in the SET clause, or DBA authority. In

addition, the owner of the view must have UPDATE or OWNER authority with respect

to the view's deﬁnition, or the owner must have DBA authority.

• Using UPDATE with views requires that the views be updatable. See "Updatability of

Queries" in the "SQL Queries" chapter.

Example

UPDATE PurchDB.Parts SET SalesPrice = SalesPrice * 1.25

WHERE SalesPrice > 500.00