delete

Overview

The delete statement is used to delete one or more columns or rows from one or more DSETs.

Syntax

delete columns [except]ColName1 [... ColNameN]

delete blankcolumns

delete rows

delete dsetdset_id

Details

Deleting columns

The list of column names following the initial delete columns statement may be used in one of two ways:

1. The columns listed will be deleted

2. All columns except those listed will be deleted

Which method to use is determined by the presence (or otherwise) of the except keyword:

delete columns One Two Three         # Delete the listed columns
delete columns except One Two Three  # Delete all but the listed columns

When using the except keyword, all column names following it must belong to the same DSET

It is not possible to delete a column from a DSET that only contains a single column. When deleting a column, the memory used to index it, along with the memory used to store the contents of its cells is released. This may be useful in situations where memory is limited.

Deleting unwanted columns early in a Transcript task will also increase the performance of many subsequent operations performed by the task.

The second keyword may be column instead of columns. Either way, the statement behaves identically.

Deleting blank columns

The delete blankcolumns statement will delete columns that only contain blank values from the default DSET.

It is not possible to completely empty a DSET using delete blankcolumns. In the event that the last remaining column in a DSET is blank then it will not be deleted.

Deleting rows

In order to delete rows from a DSET a local filter must be in effect. A local filter is created using the where statement.

When used within the body of a where statement, delete rows will delete all rows in the DSET associated with the local filter where the condition is true. For more information on conditions, please refer to the where article.

Either delete rows or delete row may be used (both variations work in an identical manner)

Deleting a DSET

The delete dset statement can be used to remove a DSET from memory. This may be useful in cases where a DSET is no longer required, for example after it has been used to enrich another DSET via the correlate statement.

It is not possible to delete the default DSET.

Examples

Delete the column temp from the default DSET and the column Interim from the azure.Usage DSET: delete columns temp azure.Usage.Interim

Delete any columns in the default DSET that have no values in any rows: delete blankcolumns

Delete all rows where the VMID is 1234

where ([VMID] == 1234) {
    delete rows
}

Delete the DSET azure.rates delete dset azure.rates