Choosing between positioned or searched UPDATE and DELETE statements in the Control Center

Use the General page of the Tools Settings notebook to specify whether to use searched or positioned UPDATE and DELETE statements in the Control Center.

About this task

There are two forms of DELETE and UPDATE statements:

Searched DELETE and UPDATE statements are used to delete or update one or more rows (optionally determined by a search condition).
Positioned DELETE and UPDATE statements are used to delete or update exactly one row (as determined by the current position of a cursor).

The Command Editor's Query Results tab and the Control Center's Open Table, Open View, and Open Alias pages perform updates and deletes when you directly edit the contents of table cells or delete rows in the table. By default, positioned DELETE and UPDATE statements are performed, based on the contents of the current result set.

Important: There is the possibility of inadvertent data corruption with UPDATE and DELETE statements in the Control Center or Command Editor if you do not include enough columns to uniquely identify the rows to be updated or deleted.

Procedure

To choose the form of DELETE and UPDATE statements in the Control Center:

Open the Tools Settings notebook by clicking the on the DB2® toolbar.
On the General page, choose one of the following options:
- Perform searched UPDATEs and DELETEs on editable query results (can affect one or more row).
- Perform positioned UPDATEs and DELETEs on editable query results (affects exactly one row).

Example

Example 1: Move a clerk who has been in the company for more than two years to department 30.

Within the Control Center, choose to perform positioned UPDATE and DELETE statements, then perform the following query against the SAMPLE database in the Command Editor:

SELECT JOB, DEPT FROM STAFF WHERE YEARS > 2 and JOB='Clerk'

Choose one of the employees in the Query Results tab and update their department number to 30. When you click Commit, the department number is updated for the first employee matching the conditions in the Query Results.

Note: In this example, it is possible for an employee with two or less years to be moved to department 30. This situation can occur because the query did not generate a result set that uniquely identified the employees. To avoid that problem, perform the update from the result set of one of the following queries instead:

SELECT JOB, DEPT, ID FROM STAFF WHERE YEARS >2 and JOB='Clerk'
SELECT JOB, DEPT, YEARS FROM STAFF WHERE YEARS >2 AND JOB='Clerk'

Example 2: Change the job titles of all SALES employees in department 66.

Within the Control Center, choose to perform searched UPDATE and DELETE statements, then perform the following query against the SAMPLE database in the Command Editor:

SELECT JOB, DEPT FROM STAFF WHERE DEPT=66

Choose one of the rows in the Query Results tab where the JOB column has a value of "Sales". Update the value in that row to a new value. When you click Commit, all employees matching the conditions in the Query Results tab have their job titles updated to the specified value. Rerun the query to see the updated values.

Note: If you had not specified the DEPT column in the SELECT clause, employees with sales jobs in all of the departments would have been affected by the UPDATE. It is important to include enough columns in the Query Results to uniquely identify the rows to be updated or deleted.

Example 3: Remove all clerks with less than two years in the company from the STAFF table

Within the Control Center, choose to perform searched UPDATE and DELETE statements, then perform the following query against the SAMPLE database in the Command Editor:

SELECT JOB, YEARS FROM STAFF WHERE JOB='Clerk' AND (YEARS<2 OR YEARS IS NULL)

Choose one of the rows in the Query Results tab where the YEARS column has a NULL value. Click Delete Row. Then select the row where YEARS=1 and click Delete Row. When you click Commit, all four rows meeting these conditions are deleted.

Note: If you had not specified the JOB column in the SELECT clause, non-clerk deletions would have been attempted as well, resulting in an SQL0438N in this example, due to the DO_NOT_DEL_SALES trigger.