Neoview SQL Reference Manual (R2.4)

SET Clause Restrictions and Error Cases
The SET clause has the following restrictions:
• The number of columns on the left side of each assignment operator should match the
number of values or SELECT list elements on the right side. The following examples are not
allowed:
UPDATE t SET (a,b)=(10,20,30)
UPDATE t set (b,c)=(SELECT r,t,s FROM x)
• If multi-column update syntax is specified and the right side contains a subquery, only one
element, the subquery, is not allowed.
UPDATE t SET (a,b)=(10, (SELECT a FROM t1))
• More than one subquery is not allowed if multiple-column syntax is used.
UPDATE t SET (a,b)=(SELECT x,y FROM z), (c,d)=(SELECT x,y FROM a))
• If a subquery is used, it must return at most one row.
SET ON ROLLBACK Considerations
The SET ON ROLLBACK expression is evaluated when each row is processed during execution
of the UPDATE statement. The results of the evaluation are applied when and if the transaction
is rolled back. This has two important implications:
• If the SET ON ROLLBACK expression generates an error (for example, a divide by zero or
overflow error), the error is returned to the application when the UPDATE operation executes,
regardless of whether the operation is rolled back.
• If an UPDATE operation is applied to a set of rows and an error is generated while executing
the UPDATE operation, and the transaction is rolled back, the actions of the SET ON
ROLLBACK clause apply only to the rows that were processed by the UPDATE operation
before the error was generated.
SET ON ROLLBACK Restrictions
The columns used in the SET ON ROLLBACK clause:
• Must be declared as NOT NULL.
• Cannot use the VARCHAR data type.
• Cannot be used in the primary key or clustering key.
Examples of UPDATE
• Update a single row of the ORDERS table that contains information about order number
200300 and change the delivery date:
UPDATE sales.orders
SET deliv_date = DATE '2008-05-02'
WHERE ordernum = 200300;
• Update several rows of the CUSTOMER table:
UPDATE sales.customer
SET credit = 'A1'
WHERE custnum IN (21, 3333, 324);
• Update all rows of the CUSTOMER table to the default credit 'C1':
UPDATE sales.customer
SET credit = 'C1';
• Update the salary of each employee working for all departments located in Chicago:
UPDATE persnl.employee
SET salary = salary * 1.1
WHERE deptnum IN
200 SQL Statements