Building UPDATE statements

Build UPDATE statements to update existing rows in a database table.

Procedure

  1. Click the Update tab.
  2. Drag the table whose rows you want to update from the repository tree to the canvas. You must have previously placed the table definitions in the IBM® InfoSphere® DataStage® repository. The easiest way to do this is to import the definitions directly from your relational database.
  3. Specify the columns that you want to update on the column selection grid. You can drag selected columns from the table, double-click a column, or drag all columns.
  4. For each column in the column selection grid, specify how values are derived. You can type a value or select a derivation method from the drop-down list. Enclose strings in single quotation marks.
    • Job Parameters. The Parameter dialog box appears. Select from the job parameters that are defined for this job.
    • Lookup Columns. The Lookup Columns dialog box appears. Select a column from the input columns to the stage that you are using the SQL builder in.
    • Expression Editor. The Expression Editor opens. Build an expression that derives the value.
  5. If you want to refine the update you are performing, choose a predicate from the Predicate list in the filter panel. Then use the expression editor to specify the actual filter (the fields displayed depend on the predicate you choose). For example, use the Comparison predicate to specify that a column should match a particular value, or the Between predicate to specify that a column falls within a particular range. The filter appears as a WHERE clause in the finished statement.
  6. Click the Add button in the filter panel. The filter that you specify appears in the filter expression panel and is added to the update statement that you are building.
  7. Click on the Sql tab to view the finished query.