Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.0

# Modifying Rows in a Table Using the SQL UPDATE Statement

Procedure

1. Formulate a SQL UPDATE string for modifying the rows of a table.

2. Call the `createStatement(`) method of the `Session` object. Pass the SQL UPDATE string as an argument.

3. If the SQL UPDATE string contains parameter markers, call the `Statement` methods to set the parameter values.
4. Run the UPDATE 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 changed in the table.

6. Optionally, set different parameter values and rerun the UPDATE statement by repeating steps 3 to 5.

7. Close the `Statement` object.

## Updating Large Number of Rows

Warning: When updating a large number of rows in a table, TIBCO recommends updating batches of rows versus the entire set of rows.

To update a large number of rows from a table, design a SQL UPDATE statement as follows:

1. Design a range query that is bounded on both ends to select a subset of rows to be updated in 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 query range that results in a subset of rows being updated 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 UPDATE.

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.