This section describes how to build a basic searched UPDATE statement, and then refine it if necessary by adding a WHERE clause.

Building a Basic Statement

The following searched UPDATE statement updates all rows in the Salary and Comm (commission) columns in the Staff table. DB/Assist uses parameter markers (question marks) to indicate values that you can supply later.

UPDATE DB2ADMIN.STAFF SET SALARY=?, COMM=?

To build this searched UPDATE statement, follow these steps:

1.Select SQL Statement Ø Tables or the Tables button.
The UPDATE/SET Clause dialog box appears, listing the tables in the connected database.

2.Select the STAFF table in the Table list box. (You can only select columns from a single table when building an UPDATE statement.)
The columns in the STAFF table appear in the Columns list box

3.Select SALARY and COMM in the Columns list box, and then select the > push button.
The qualified names of the columns you selected appear in the Selected Columns list box, as shown below.
If you need more information about how to select tables and columns, see Selecting Tables and Columns for SELECT UPDATE and INSERT Statements.

UPDATE/SET Clause Dialog Box

4. Select the OK push button.
The UPDATE/SET Clause dialog box closes. DB/Assist adds the UPDATE statement to the SQL Editor window and to the SAM that contains this statement.

Adding a WHERE Clause

A searched UPDATE statement, like a SELECT statement, can include a WHERE clause. The WHERE clause specifies which rows in the table to update.

To build a WHERE clause for a searched UPDATE statement, you use the same WHERE Clause dialog box that you do for a SELECT statement. Therefore, all of the procedures described in Adding a WHERE Clause for adding a WHERE clause to a SELECT statement also apply to a searched UPDATE statement.

This section gives a procedure for adding a simple WHERE clause to the searched UPDATE statement built in the preceding section. For complete information about the different types of WHERE clauses that you can build, including those with subqueries, see Adding a WHERE Clause.

The following searched UPDATE statement starts with the example built in the preceding section, and adds a WHERE clause that updates only the row where the ID number is 80.

 

UPDATE DB2ADMIN.SALARY

SET SALARY=?, COMM=? WHERE STAFF.ID=80

 

To build the WHERE clause (shown in italics) in this statement, follow these steps:

1.Select SQL Statement Ø Where or the Where button.
The WHERE Clause dialog box appears. The STAFF table and columns appear in the Table and Column lists, respectively.

1.Select ID in the Column list box.
The qualified column name appears in the Expression 1 entry field.

2.Select the = operator from the Operator list box.

3.Position the cursor in the Expression 2 entry field and type the number 80.

4.Select the Apply push button.
DB/Assists adds the WHERE clause to the current UPDATE statement in the SQL Editor window and to the SAM that contains this statement.

5.Select the Close push button or double-click on the system menu to close the WHERE Clause dialog box.