Updating Data Records

So far we have looked at the INSERT statement as a method of moving data into your DB2 table. You may want to update only a column with values for a group of data records. There is an SQL UPDATE statement that can be used to specify the column and its new values. A table or a view can be referenced as the target for the UPDATE statement.

The UPDATE statement can be used in two forms:

• Searched update— This type of UPDATE statement is used to update one or more rows in a table. It requires a WHERE clause to establish the update condition (which rows are to be updated).

• Positioned update— This kind of UPDATE statement is always embedded into a program. It uses cursors to update the row where the cursor is positioned. As the cursor is repositioned using the FETCH statement, the target row for the UPDATE statement changes.

This chapter focuses on searched updates. Positioned updates are covered in various programming interfaces in Part 2, DB2 UDB Family Application Development.

Similar to the INSERT statement, all of the database constraint mechanisms are enforced during an UPDATE statement. There can be specific update constraint triggers and referential integrity constraints that could be different from the insert constraints. For example, the following is a transaction that updates employee '000100' with a 6% salary increase:

UPDATE employee
SET salary = salary * 1.06
WHERE empno = '000100'

It is very important that you provide the proper WHERE clause to avoid updating unintended data records. In this example, we needed to specify the predicate empno='000100' to avoid changing the salary of any of the other employees.

The UPDATE statement also can be used with fullselects. In this case, the fullselect must return a row with exactly the same number of columns and column data types of the row that will be updated. Observe that this fullselect must return only one row.

Let's update a row using a SELECT statement to set the new value. Using the employee table as a basis, let's update the staff table with information about the salary and commission.

UPDATE staff
SET (salary, comm) =
     (SELECT salary, comm
      FROM employee
      WHERE staff.id = INTEGER(employee.empno))
WHERE id = 150

This example updates two different columns in the same operation. These columns are indicated in the parentheses following the SET clause. After indicating which columns are going to be updated, a SELECT statement retrieves the salary and commission values for the employee. Notice the last WHERE clause in the statement restricts the rows that will be updated.

The SQL statement that updates the salary and comm columns is known as a row fullselect. This name is given because it returns only one row. Observe that the scalar fullselect can be considered a special case of a row fullselect.

Updating Large Amounts of Data

There are times when you need to update a large number of rows of a particular table. This can be accomplished by issuing a searched update. However, this also could allocate a large amount of transactional log space. You can accomplish updates using positioned updates, where you can easily control the commit frequency. This can be accomplished using a programming interface such as embedded SQL. We talk more about this in Part 2 of this book.