SQL-J Language Reference
Page 20 of 121

DELETE statement

Syntax

{
    DELETE FROM { TableName | ExternalVirtualTable }
        [ WHERE clause] |
    DELETE [FROM TableName | ExternalVirtualTable]
        WHERE CURRENT OF clause
}

The first syntactical form, called a searched delete, removes all rows identified by the table name and WHERE clause.

The second syntactical form, called a positioned delete, deletes the current row of an open, updatable cursor. If there is no current row or if it no longer satisfies the cursor's query, an exception is raised. For more information about updatable cursors, see SELECT statement.

NOTE: There is no transaction support for inserting into or deleting from an external virtual table; rolling back a transaction that executed statement that inserted into or deleted from an ExternalVirtualTable does not roll back the underlying insert or delete.

DELETE Examples

DELETE FROM HotelBookings
WHERE arrival < CURRENT_DATE

-- deleting from a read-write VTI
DELETE FROM NEW JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable(
    'jdbc:cloudscape:history', 'hotelbookings')
WHERE hotel_id = 1000 and group_id = 500

stmt.executeUpdate("DELETE WHERE CURRENT OF " +
    resultSet.getCursorName())

Dependency System

A searched delete statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), and any other table named in the WHERE clause. A CREATE or DROP INDEX statement for the target table of a prepared searched delete statement invalidates the prepared searched delete statement.

The positioned delete statement depends on the cursor and any tables the cursor references. You can compile a positioned delete even if the cursor hasn't been opened yet. However, removing the open cursor with the JDBC close method invalidates the positioned delete.

A CREATE or DROP INDEX statement for the target table of a prepared positioned delete invalidates the prepared positioned delete statement.

Searched delete statements depend on all aliases used in a statement. Dropping an alias invalidates a prepared delete statement if the statement uses alias.

DROP Statements