PURPOSE: This statement removes one or more rows of data from the specified table. Rows may be deleted from one table based on values in other tables.
If the WHERE clause is not specified, the entire table is cleared as is done for an ACCENT R CLEAR DS command.
SQL DELETE /FROM/ table_name [WHERE str_exp, CURRENT ROW] [HUSH]
|
table_name |
is the name of the table from which you want to remove rows. |
|
WHERE str_exp |
is a quoted literal, a character or an alphabetic field, a function, or a concatenation of any of the preceding that evaluates to a string that sets the search condition for the rows that are to be deleted. This string can be built dynamically from within the Process Module. You can set the string to any value that yields a valid WHERE clause including column names, expressions, arithmetic operators, comparison operators, the keywords NOT, LIKE, IS NULL, AND, OR, BETWEEN, IN, EXISTS, ANY, ALL, sub-queries, or any combination of these items. There is no limit on the number of search conditions that can be included. This search condition cannot contain aggregates. See the ‘WHERE clause’ in the Oracle SQL Language Reference Manual. |
|
Current Row |
is the row that was last fetched via the SQL FETCH statement. This clause is mutually exclusive with the WHERE clause. |
|
shuts off warning, error, auxiliary, and SQL messages for the execution of that statement only. The text of the error message can be found in the system function @ERROR_MESSAGE. |
SQL SELECT * FROM BOOKS_ORA WHERE "BOOKS_ORA.TYPE_CODE = 'for_lang'"
START:10
SQL FETCH NEXT ROW
LEAVE:10 IF @SQLCODE # 0
SQL DELETE FROM BOOKS_ORA CURRENT ROW
REPEAT:10
SQL COMMIT WORK
Notes: Oracle does not allow rows of a set established with the ORDER BY clause of the SQL SELECT statement or by joining two or more tables to be deleted. The system field @SQLCODE contains the return status of the SQL DELETE statement. The system field @SQL_DELETE contains the number of rows deleted by the SQL DELETE statement.
SEE ALSO: OSQL DELETE command