Deleting data by using DELETE statements

You can use the DELETE statement to remove entire rows from a table.

The DELETE statement removes zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause. If you omit a WHERE clause from a DELETE statement, Db2 removes all rows from the table or view you name. Therefore, use the DELETE statement carefully. The DELETE statement does not remove specific columns from the row.

Begin general-use programming interface information.

Example DELETE statements

The following statement deletes each row in the NEWEMP table that has employee number 000060.

DELETE FROM NEWEMP
  WHERE EMPNO = '000060';

Assume that the statements in the following examples are embedded in PL/I programs.

From the table DSN8C10.EMP, delete the row on which the cursor C1 is currently positioned.
```
  EXEC SQL DELETE FROM DSN8C10.EMP WHERE CURRENT OF C1;
```

From the table DSN8C10.EMP, delete all rows for departments E11 and D21.

  EXEC SQL DELETE FROM DSN8C10.EMP
    WHERE WORKDEPT = 'E11' OR WORKDEPT = 'D21';

From employee table X, delete the employee who has the most absences.

  EXEC SQL DELETE FROM EMP X
    WHERE ABSENT = (SELECT MAX(ABSENT) FROM EMP Y
    WHERE X.WORKDEPT = Y.WORKDEPT);

Assuming that cursor CS1 is positioned on a rowset consisting of 10 rows of table T1, delete all 10 rows in the rowset.
```
EXEC SQL DELETE FROM T1 WHERE CURRENT OF CS1; 
```
Assuming cursor CS1 is positioned on a rowset consisting of 10 rows of table T1, delete the fourth row of the rowset.
```
EXEC SQL DELETE FROM T1 WHERE CURRENT OF CS1 FOR ROW 4 OF ROWSET;
```

Delete rows in table T1 if the value for column COL2 matches the cardinality of array INTA. The array INTA is specified as an argument for the CARDINALITY function in the DELETE statement.

CREATE TYPE INTARRAY AS INTEGER ARRAY[6];
CREATE VARIABLE INTA AS INTARRAY;
SET INTA = ARRAY[1, 2, 3, 4, 5];
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
INSERT INTO T1 VALUES('abc', 10);
DELETE FROM T1 WHERE COL2 = CARDINALITY(INTA);

Delete only 3 rows from table T1 where the value of column C2 is greater than 10.
```
DELETE FROM T1
   WHERE C2 > 10
   FETCH FIRST 3 ROWS ONLY;
```

End general-use programming interface information.