DELETE (DATALAKE) statement

The DELETE statement deletes rows from an Iceberg Datalake table. The DELETE statement is not supported for Hive Datalake tables.

Only a searched DELETE is supported for Iceberg Datalake tables. A positioned delete is not supported.

Invocation

A DELETE statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

To execute this statement, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • DELETE privilege on the table from which rows are to be deleted
  • CONTROL privilege on the table from which rows are to be deleted
  • DELETEIN privilege on the schema containing the table from which rows are to be deleted
  • Schema DATAACCESS authority on the schema containing the table table from which rows are to be deleted
  • DATAACCESS authority
The privilege held by the authorization ID of the statement must also include at least one of the following authorities for each table, view or nickname referenced by a subquery:
  • SELECT privilege
  • CONTROL privilege
  • SELECTIN privilege on the schema containing the table
  • Schema DATAACCESS authority on the schema containing the table
  • DATAACCESS authority

Group privileges are not checked for static DELETE statements.

Syntax (searched-delete)

Read syntax diagramSkip visual syntax diagramDELETE FROMtable-nameWHEREsearch-condition order-by-clause1 offset-clause fetch-clause
Notes:
  • 1 If the order-by-clause is specified, either the offset-clause or fetch-clause must also be specified (SQLSTATE 42601).

Description

FROM table-name

Identifies the Iceberg Datalake table that is the object of the delete operation.

WHERE
Specifies a condition that selects the rows to be deleted. The clause can be omitted or a search condition specified. If the clause is omitted, all rows of the table or view are deleted.
search-condition
Each column-name in the search condition, other than in a subquery must identify a column of the table.

The search-condition is applied to each row of the table, and the deleted rows are those for which the result of the search-condition is true.

order-by-clause
Specifies the order of the rows for application of the offset-clause and fetch-clause. Specify an order-by-clause to ensure a predictable order for determining the set of rows to be deleted based on the offset-clause and fetch-clause. For details on the order-by-clause, see order-by-clause.
offset-clause
Limits the effect of the delete by skipping a subset of the qualifying rows. For details on the offset-clause, refer to offset-clause.
fetch-clause
Limits the effect of the delete to a subset of the qualifying rows. For details on the fetch-clause, refer to fetch-clause.

Notes

  • If an error occurs during the execution of a multiple row DELETE, no changes are made to the table.
  • SQLERRD(3) in the SQLCA shows the number of rows that qualified for the delete operation. In the context of an SQL procedure statement, the value can be retrieved using the ROW_COUNT variable of the GET DIAGNOSTICS statement. SQLERRD(5) in the SQLCA shows the number of rows affected by referential constraints and by triggered statements. It includes rows that were deleted as a result of a CASCADE delete rule and rows in which foreign keys were set to the null value as the result of a SET NULL delete rule. With regards to triggered statements, it includes the number of rows that were inserted, updated, or deleted.
  • Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products. These alternatives are non-standard and should not be used.
    • The FROM keyword can be omitted.