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

Building a Basic Statement

The following searched DELETE statement removes all rows in the Staff table:

 

DELETE FROM DB2ADMIN.STAFF

To build this searched DELETE statement, follow these steps:

1.Select SQL Statement Ø Tables or the Tables button.
The DELETE/FROM 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 singe table when building a DELETE statement.)
The names of all columns in the STAFF table appear selected in the Columns list box, as shown below. (You cannot select individual columns when building a DELETE statement.)

DELETE/FROM Clause Dialog Box

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

The searched DELETE statement you just built removes all rows in the STAFF table. To remove only those rows that meet certain search conditions, you must add a WHERE clause, as described in the next section.

Adding a WHERE Clause

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

To build a WHERE clause for a searched DELETE 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 DELETE statement.

This section gives a procedure for adding a simple WHERE clause to a searched DELETE statement. 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 DELETE statement includes a WHERE clause that removes only those in the STAFF table rows where the years are less than 5.

 

DELETE FROM DB2ADMIN.STAFF WHERE YEARS < 5

 

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.

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

3.Select the < operator from the Operator list box.

4.Position the cursor in the Expression 2 entry field and type the number 5.

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

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