ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 591

SQL Statements S - Z

UPDATE WHERE CURRENT

• 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, authority needed 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.

Example

A cursor for use in updating values in column QtyOnHand is declared and opened.

DECLARE NewQtyCursor CURSOR FOR

SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory

FOR UPDATE OF QtyOnHand

OPEN NewQtyCursor

Statements setting up a FETCH-UPDATE loop appear next.

FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul

Statements for displaying a row to and accepting a new QtyOnHand value from a user go

here. The new value is stored in :NewQty.

UPDATE PurchDB.Inventory\

SET QtyOnHand = :NewQty\

WHERE CURRENT OF NewQtyCursor

CLOSE NewQtyCursor