Table of Contents

Search

  1. Preface
  2. Using the Designer
  3. Working with Sources
  4. Working with Flat Files
  5. Working with Targets
  6. Mappings
  7. Mapplets
  8. Mapping Parameters and Variables
  9. Working with User-Defined Functions
  10. Using the Debugger
  11. Viewing Data Lineage
  12. Comparing Objects
  13. Managing Business Components
  14. Creating Cubes and Dimensions
  15. Using the Mapping Wizards
  16. Datatype Reference
  17. Configure the Web Browser

Rules and Guidelines for Configuring the Target Update Override

Rules and Guidelines for Configuring the Target Update Override

Use the following rules and guidelines when you enter target update queries:
  • If you use target update override, you must manually put all database reserved words in quotes.
  • You cannot override the default UPDATE statement if the target column name contains any of the following characters:
    ' , ( ) < > = + - * / \ t \ n \ 0 <space>
  • You can use parameters and variables in the target update query. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the UPDATE statement, or you can use a parameter or variable as the update query. For example, you can enter a session parameter, $ParamMyOverride, as the update query, and set $ParamMyOverride to the UPDATE statement in a parameter file.
  • When you save a mapping, the Designer verifies that you have referenced valid port names. It does not validate the SQL.
  • If you update an individual row in the target table more than once, the database only has data from the last update. If the mapping does not define an order for the result data, different runs of the mapping on identical input data may result in different data in the target table.
  • A WHERE clause that does not contain any column references updates all rows in the target table, or no rows in the target table, depending on the WHERE clause and the data from the mapping. For example, the following query sets the EMP_NAME to “MIKE SMITH” for
    all
    rows in the target table if any row of the transformation has EMP_ID > 100:
    UPDATE T_SALES set EMP_NAME = 'MIKE SMITH' WHERE :TU.EMP_ID > 100
  • If the WHERE clause contains no port references, the mapping updates the same set of rows for each row of the mapping. For example, the following query updates all employees with EMP_ID > 100 to have the EMP_NAME from the last row in the mapping:
    UPDATE T_SALES set EMP_NAME = :TU.EMP_NAME WHERE EMP_ID > 100
  • If the mapping includes an Update Strategy or Custom transformation, the Target Update statement only affects records marked for update.
  • If you use the Target Update option, configure the session to mark all source records as update.


Updated July 02, 2020