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 .
Using the ONLY keyword with typed tablesWhen the DELETE statement specifies a supertable, any qualifying rows that satisfy the WHERE clause are also deleted, by default, from all the subtables of the supertable within the table hierarchy. To restrict the scope of DELETE to the supertable, you must specify the ONLY keyword before the name or synonym of the supertable.

Using the ONLY keyword with typed tables

When the DELETE statement specifies a supertable, any qualifying rows that satisfy the WHERE clause are also deleted, by default, from all the subtables of the supertable within the table hierarchy. To restrict the scope of DELETE to the supertable, you must specify the ONLY keyword before the name or synonym of the supertable.

In the following example, any rows whose name column has the value johnson will be deleted from the super_tab supertable. Any rows with johnson in their name column will persist, however, in the subtables of super_tab, because the ONLY( ) clause restricts the DELETE operation to that supertable:

DELETE FROM ONLY(super_tab)   -- scope excludes child tables
   WHERE name = "johnson";

Warning: If you use the DELETE statement on a supertable and omit the ONLY keyword and the WHERE clause, all rows of the supertable and of all its subtables are deleted, as in the following example.

DELETE FROM super_tab;   -- deletes all rpws in the  hierarchy

You cannot specify the ONLY keyword if you plan to use the WHERE CURRENT OF clause to delete the current row of the active set of a cursor.