SQL/MX Programming Manual for Java

SQLJ Programming
HP NonStop SQL/MX Programming Manual for Java—523726-003
3-55
Positioned UPDATE and DELETE Statements
Positioned UPDATE and DELETE Statements
In an SQLJ program, use a positioned UPDATE and DELETE statement with an
iterator in a data retrieval loop to update or delete multiple rows in a table. Positioned
UPDATE and DELETE statements specify the name of an iterator in the WHERE
CURRENT OF clause. For the syntax, see the SQL/MX Reference Manual.
In an iterator declaration clause, use the implements sqlj.runtime.ForUpdate
clause to indicate that this is an updatable iterator. Without this clause, the iterator is
read-only. For the syntax, see Iterator Declaration Clause on page A-2.
In the iterator declaration clause, you can optionally specify a list of columns in the
iterator result set that a positioned UPDATE or DELETE statement can update. List the
updatable iterator columns by using the WITH clause element named
updateColumns. For more information, see the with-clause on page A-3. If you do
not specify updateColumns, all the columns in the result set are updatable for an
iterator that implements the ForUpdate interface.
In an iterator assignment clause, include a FOR UPDATE clause in the specification of
the iterator object. Specify the FOR UPDATE clause at the end of the query expression
before the closing curly brace (}). For the syntax, see Assignment Clause on
page A-5.
The query expression in an updatable iterator must involve a single table and simple
scan. Updatable iterators disallow joins, unions, aggregates, and so on.
Example
This example shows an updatable named iterator:
// Declare named iterator class NamIter for update,
// which has accessor methods Last_Name() and Salary()
#sql public iterator NamIter implements sqlj.runtime.ForUpdate
(String Last_Name, double Salary);
// Declare an iterator variable of NamIter class
NamIter iter;
// Other Java variable declarations and initializations
String empname = null;
double avgsalary = 0;
double salary = 0;
// Find the average salary
#sql {SELECT AVG(salary) INTO :avgsalary
FROM samdbcat.persnl.employee};
// Assign the result set of a query to an iterator object
// named iter that is specified to be FOR UPDATE
#sql iter = {SELECT last_name, salary
FROM samdbcat.persnl.employee
FOR UPDATE};