Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# Removing Rows From a Table Using the SQL DELETE Statement

Procedure

1. Formulate a SQL DELETE string for removing rows of data from a table.

2. Call the `createStatement()` method of the `Session` object. Pass the SQL DELETE string as an argument.
3. If the SQL DELETE string contains parameter markers, call the `Statement` methods to set the parameter values.
4. Run the DELETE statement by calling the `executeUpdate()` method of the `Statement` object.

5. Check the result of the `executeUpdate()` method to verify that the correct number of rows have been removed from the table.

6. Set different parameter values and rerun the DELETE statement by repeating steps 3 to 5.

7. Close the `Statement` object.

## Deleting large number of rows

When removing a large number of rows from a table, TIBCO recommends to remove batches of rows versus the entire set of rows.

To delete a large number of rows from a table, design a SQL DELETE statement as follows:

1. Design a range query which is bounded on both ends to select a subset of rows to be removed from the table.

2. Avoid unbounded range queries to reduce the number of rows scanned. For example, use a WHERE clause similar to the following:

   WHERE key >= 0 AND key <=1000

   WHERE key BETWEEN 0 AND 1000

3. Use parameters in the range query which results in a subset of rows being removed each time `executeUpdate()` is invoked. For example,

   WHERE key BETWEEN ? AND ?

4. Design the WHERE clause so that an index is used for finding the rows and prevent full table scans.

5. Set the statement property `TIBDG_STATEMENT_PROPERTY_DOUBLE_UPDATE_TIMEOUT` so the `executeUpdate` requests do not timeout.

   The SQL LIMIT clause is not part of the syntax for SQL DELETE.

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.