IBM PureData System for Analytics, Version 7.1

DELETE

Use the DELETE command to delete rows from a table.

Syntax

Syntax for the DELETE command:

DELETE FROM <table> [ WHERE <condition> ]

Inputs

The DELETE command takes the following inputs:

Table 1. DELETE inputs
Input	Description
<table>	The name of the table from which rows are to be deleted. You can use a table alias. For example: `DELETE FROM tablename t1 WHERE t1.c1=2;`
<condition>	A SQL SELECT statement that determines which rows are to be deleted. If you do not specify a WHERE clause, the system deletes all rows in the table. This results in a valid but empty table.

Output

The DELETE command has the following output:

Table 2. DELETE output
Output	Description
`DELETE <count>`	The indicated number of items were successfully deleted. If the count is zero, no rows were deleted.

Privileges

You must be the admin user, the owner of the database or schema where the table is defined, the table owner, or your account must have the Delete privilege.

Usage

The following provides sample usage.

Remove all but musicals from the table films:

MYDB.SCH1(USER)=> DELETE FROM films WHERE kind <> 'Musical';

Display the remaining rows of the table films:

MYDB.SCH1(USER)=> SELECT * FROM films;
   code    | title                     | did | date_prod  | kind    | len
   --------+---------------------------|-----|------------+---------+------
   UA501   | West Side Story           | 105 | 1961-01-03 | Musical | 02:32:00
   TC901   | The King and I            | 109 | 1956-08-11 | Musical | 02:13:00
   WD101   | Bed Knobs and Broomsticks | 111 |            | Musical | 01:57:00

Clear the table films:
```
MYDB.SCH1(USER)=> DELETE FROM films;
```

Display the valid but empty table films:

MYDB.SCH1(USER)=> SELECT * FROM films;
   code   | title   | did   | date_prod   | kind   | len
   -------+---------+-------+-------------+--------+------