Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
DELETE statementUse the DELETE statement to delete one or more rows from a table, or to delete one or more elements from a collection variable of SPL or of .
Deleting Rows That Contain Collection Data Types

Deleting Rows That Contain Collection Data Types

When a row contains a column that is a collection data type (LIST, MULTISET, or SET), you can search for a particular element in the collection, and delete the row or rows in which the element is found.

For example, the following statement deletes any rows from the new_tab table in which the set_col column contains the element

jimmy
smith

DELETE FROM new_tab WHERE 'jimmy smith' IN set_col;

You can also use a collection variable to delete values in a collection column by deleting one or more individual elements in a collection. For more information, see Collection-Derived Table and the examples in Database Name and Example of Deleting from a Collection.