Hitachi

Hitachi Advanced Database SQL Reference

4.1.1 Specification format and rules for the DELETE statement

The DELETE statement deletes rows that satisfy the specified search conditions.

Organization of this subsection

(1) Specification format

DELETE-statement ::= DELETE FROM table-name [[AS] correlation-name] [WHERE search-conditions]

To Page Top

(2) Explanation of specification format

[Figure] table-name

Specifies the name of the table whose rows you want to delete (the deletion target table). For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify a read-only viewed table.

[Figure] [AS] correlation-name

Specifies the correlation name of the deletion target table. For details about correlation names, see (4) Table specification format in 6.1.5 Qualifying a name. For details about the effective scope of correlation names, see 6.8 Scope variables.

[Figure] WHERE search-conditions

Specifies the conditions that identify the rows to be deleted in search-conditions. For details about search conditions, see 7.18 Search conditions.

If the WHERE clause is omitted, all the rows in the specified table are deleted.

The following rules apply:

  • You can specify dynamic parameters in the search conditions.

If you specify an updatable viewed table in table-name, note the following points:

  • When you delete rows from the updatable viewed table, the rows of the underlying table are deleted.

  • The rows that are deleted from the underlying table are the rows that satisfy both the search conditions specified when the viewed table was defined and the search conditions specified here.

  • If the WHERE clause is omitted, the rows that are deleted from the underlying table are rows that satisfy the search conditions specified when the viewed table was defined.

To Page Top

(3) Privileges required at execution

To execute the DELETE statement, all of the following privileges are required:

Example
DELETE FROM "T1"
    WHERE "T1"."C1" IN (SELECT "C1" FROM "T2" WHERE "C3"<=100)

The DELETE privilege for Table T1 and the SELECT privilege for Table T2 are required to execute the above DELETE statement.

To Page Top

(4) Rules

  1. If there are no rows that meet the deletion conditions, SQLCODE is set to 100.

  2. The total number of tables, derived tables, and table function derived tables specified in the DELETE statement cannot exceed 2,048. For rules on how to count the number of tables, derived tables, and table function derived tables specified in an SQL statement, see (4) Rules in 4.4.1 Specification format and rules for the SELECT statement.

  3. If the set operations specified in the DELETE statement are all UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.

  4. A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the DELETE statement.

  5. This statement cannot be used to delete rows from a dictionary table or system table.

  6. The table containing the rows to be deleted cannot be specified in the FROM clause of a subquery within the search conditions.

  7. The same operation or design that can be used when the DELETE statement is run for row store tables cannot be used when the statement is run for column store tables. For details, see Criteria for selecting row store tables and column store tables, Checking whether a single-chunk table needs to be reorganized, and Checking whether a multi-chunk table needs to be reorganized in the HADB Setup and Operation Guide.

  8. This statement cannot delete archived rows. The DELETE statement that is run to delete archived rows will result in an error. To delete archived rows, first, unarchive the chunk that stores the rows to be deleted. Then, run the DELETE statement to delete the rows.

  9. The DELETE statement can delete unarchived rows. Note, however, that the DELETE statement you run must meet all of the following conditions:

    • The archive range column is specified in a search condition.

    • In the search condition in which the archive range column is specified, only a comparison predicate, the IN predicate, or the BETWEEN predicate is specified.

    • OR, NOT, and other logical operators are not used in the search condition in which the archive range column is specified.

    • Archived rows are not specified as the deletion-target rows.

    Unless all of the preceding conditions are met, the DELETE statement will result in an error.

    Important

    In the search condition in which the archive range column is specified, the predicates that can be specified are limited. Even if logical operators such as OR and NOT are not specified in the search condition, the DELETE statement might result in an error. For details, see Using the datetime information of the archive range column to narrow the search range in the HADB Application Development Guide.

    The following shows typical examples in which the DELETE statement can be run and cannot be run. Note that in the following examples, table ARCHIVE-T1 is an archivable multi-chunk table, and column RECORD-DAY is the archive range column.

    Example in which the DELETE statement can be run

    Example:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016/02/01' AND DATE'2016/02/29'
      AND "CODE"='P001'

    In the preceding example, the DELETE statement can be run because all of the following conditions are met:

    • The archive range column (RECORD-DAY) is specified in a search condition.

    • Only the BETWEEN predicate is specified in the search condition in which the archive range column is specified.

    • OR, NOT, and other logical operators are not used in the search condition in which the archive range column is specified.

    • Archived rows are not specified as the deletion-target rows.

      [Figure]

      Important

      For comparison with the archive range column specified in a search condition, we recommend that you specify a literal.

      Example of recommended specification:

      "RECORD-DAY" BETWEEN DATE'2016/01/01' AND DATE'2016/01/10'

      "RECORD-DAY" >= DATE'2016/02/10'

      We recommend that you specify only a literal.

      Example of specification that is not recommended:

      "RECORD-DAY" BETWEEN ? AND ?

      "RECORD-DAY" >= CURRENT_DATE

      Note

      The HADB server determines whether the deletion-target data has been archived from the search condition in which the archive range column is specified. If you specify a literal as the comparison with the archive range column, you can reduce the time required for determination. If you do not specify a literal, determination might require a very long time.

    Examples in which the DELETE statement cannot be run

    • No archive range column is specified in search conditions

    Example 1:

    DELETE FROM "ARCHIVE-T1"

    In this example, because the archive range column (RECORD-DAY) is not specified in the search condition, the DELETE statement results in an error.

    [Figure]

    Example 2:

    DELETE FROM "ARCHIVE-T1"
    WHERE "CODE"='P001'

    In this example, because the archive range column (RECORD-DAY) is not specified in the search condition, the DELETE statement results in an error. An error occurs even when an attempt is made to delete unarchived rows.

    • Logical operations such as OR and NOT are specified in the search condition in which the archive range column is specified

    Example:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016-01-01' AND DATE'2016-01-31'
       OR "RECORD-DAY" BETWEEN DATE'2016-03-01' AND DATE'2016-03-31'

    In this example, because the OR operator is specified in the search condition in which the archive range column is specified, the DELETE statement results in an error. The preceding statement will also result in an error when an attempt is made to delete unarchived rows.

    In this case, you can delete the rows by running the DELETE statement twice as follows:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016-01-01' AND DATE'2016-01-31'
DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016-03-01' AND DATE'2016-03-31'

    [Figure]

    • Archived rows are specified as the deletion-target rows

    Example:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2015/11/01' AND DATE'2016/01/31'

    In this example, the DELETE statement results in an error because an attempt is made to delete archived rows.

    [Figure]

    • The archive range column is specified together with other items

    Example:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" - 10 DAY > DATE'2016/02/01'

    In this example, the DELETE statement results in an error because a datetime operation using the archive range column is specified.

    • A datetime operation is used in the comparison with the archive range column

    Example:

    DELETE FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" >= CURRENT_DATE - 1 MONTH

    In this example, the DELETE statement results in an error because a datetime operation is used in the comparison with the archive range column.

  10. If an archivable multi-chunk table is specified in the DELETE statement, accesses to the location table and system table (STATUS_CHUNKS) occur. At this time, locked resources are secured for the system table (STATUS_CHUNKS). For details about locks, see Locking in the HADB Setup and Operation Guide.

To Page Top

(5) Examples

Example 1

Delete rows where the customer ID (USERID) is U00212 from the customer table (USERSLIST).

DELETE FROM "USERSLIST"
    WHERE "USERID"='U00212'
Example 2

Delete rows where the date of purchase (PUR-DATE) is between September 4, 2011 and September 5, 2011 from the sales history table (SALESLIST).

DELETE FROM "SALESLIST"
    WHERE "PUR-DATE" BETWEEN DATE'2011-09-04' AND DATE'2011-09-05'

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.