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.