A positioned UPDATE statement uses the WHERE CURRENT OF clause to update a single row of data pointed to by the current cursor position. The cursor is one defined for a previously-created multi-row SELECT statement in your SAM
In order to reference a cursor in the positioned UPDATE statement, you must create a multi-row SELECT statement in your SAM before you create a positioned UPDATE statement. The positioned UPDATE statement and the multi-row SELECT statement that it references should operate on a single database table.
If you attempt to create a positioned UPDATE statement without first creating a multi-row SELECT statement, DB/Assist displays an error message and does not allow you to create the positioned UPDATE statement.
The multi-row SELECT statement can contain a FOR UPDATE OF clause that identifies all of the columns to be updated in the database table. The FOR UPDATE OF clause is optional if you check the MIA Standards Compliance box in the Bind Options dialog box, as described in Setting Bind Options. If the MIA Standards Compliance box is not checked, the FOR UPDATE OF clause is required.
For example, the following positioned UPDATE statement updates all columns in the Org table row pointed to by the current position of a cursor named SQL_CUR_4:
UPDATE DB2ADMIN.ORG
SET DEPTNUMB=?, DEPTNAME=?, MANAGER=?, DIVISION=?, LOCATION=?
WHERE CURRENT OF SQL_CUR_4
To build the required multi-row SELECT statement and the positioned UPDATE statement that references it, follow these steps:
1. Build the following multi-row SELECT statement named SelectOrgForUpdate that retrieves all rows from the Org table:
SELECT ORG.DEPTNUMB, ORG.DEPTNAME, ORG.MANAGER, ORG.DIVISION, ORG.LOCATION
FROM DB2ADMIN.ORG ORG
1.Add the following FOR UPDATE OF clause (shown in bold) to the SELECT statement by typing it in the SQL Editor window.
SELECT ORG.DEPTNUMB, ORG.DEPTNAME, ORG.MANAGER, ORG.DIVISION, ORG.LOCATION
FROM DB2ADMIN.ORG ORG
FOR UPDATE OF DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION
2.To build the positioned UPDATE statement, select SQL Statement Ø Tables or the Tables button.
The UPDATE/SET Clause dialog box appears, listing the tables in the connected database.
3.Select the ORG table in the Table list box (You can only select columns from a single table when building an UPDATE statement.)
The columns in the ORG table appear in the Columns list box.
4.Select the >> push button to select all columns in the ORG table.
The qualified names of all columns in the ORG table appear in the Selected Columns list box.
If you need more information about selecting tables and columns, see Selecting Tables and Columns for SELECT UPDATE and INSERT Statements.
5.Select the OK push button in the UPDATE/SET Clause dialog box.
DB/Assist adds the positioned UPDATE statement built so far to the SQL Editor window and to the SAM containing this statement, as follows:
UPDATE DB2ADMIN.ORG ORG
SET DEPTNUMB=?, DEPTNAME=?, MANAGER=?, DIVISION=?, LOCATION=?
The WHERE CURRENT OF Clause dialog box appears so you can build the WHERE CURRENT OF clause for this statement. This dialog box lists the statement and cursor names defined for each multi-row SELECT statement in the current SAM
1.In the WHERE CURRENT OF Clause dialog box, select a row containing a statement and cursor name from the list box, as shown below.
WHERE CURRENT OF Clause Dialog Box
2.Select the OK push button in the WHERE CURRENT OF Clause dialog box.
The dialog box closes, and DB/Assist adds the WHERE CURRENT OF clause to the UPDATE statement in the SQL Editor window, as follows:
UPDATE DB2ADMIN.ORG
SET DEPTNUMB=?, DEPTNAME=?, MANAGER=?, DIVISION=?, LOCATION=?
WHERE CURRENT OF SQL_CUR_4
For a sample ESL program that shows how to use positioned UPDATE and positioned DELETE statements, see Sample Program for Positioned SQL.
