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]
(2) Explanation of specification format
- 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.
- [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.
- 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.
-
(3) Privileges required at execution
To execute the DELETE statement, all of the following privileges are required:
-
The CONNECT privilege
-
DELETE privilege for a table whose rows are to be deleted
-
SELECT privilege for a table specified in a query expression body
- 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.
(4) Rules
-
If there are no rows that meet the deletion conditions, SQLCODE is set to 100.
-
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.
-
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.
-
A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the DELETE statement.
-
This statement cannot be used to delete rows from a dictionary table or system table.
-
The table containing the rows to be deleted cannot be specified in the FROM clause of a subquery within the search conditions.
-
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.
-
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.
-
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.
- 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.
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'
• 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.
• 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.
-
-
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.
(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'