Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.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

Warning: When removing a large number of rows from a table, TIBCO recommends removing batches of rows rather than the entire set of rows.

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

Procedure

1. Design a range query that 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 that 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 time out.

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

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

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