Update Strategy works only when you have primary key defined in the target table/ definition and when you want to update the target table based on the primary key.
What if, you have a situation to update the records in the target table based on a business key (Business Key is the combination of more than two attributes. ) or non primary key attributes.
Update Strategy will fail in this case or it will not be able to update the records with out the primary key. We use “Target Update Override“, to update even on the columns that are not primary key.
For those who do not know where this property should be enabled, its in the session –> Mapping tab –> Targets –> Properties tab –> Update Override. (This property will be visible only when the target is relational).
Here is the syntax and example for using the Update Override option. (taken from Informatica Power center 10x help)
For example, a mapping passes the total sales for each salesperson to the ZT_SALES table. In the Designer enter the following UPDATE statement for the target ZT_SALES:
UPDATE ZT_SALES SET EMP_NAME = :TU.EMP_NAME DATE_SHIPPED = :TU.DATE_SHIPPED TOTAL_SALES = :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID
Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, use :TU to specify ports.
Overriding the WHERE Clause:
You can override the WHERE clause to include non-key columns. For example, you might want to update records for employees named Mike Smith only. You can edit the WHERE clause as follows:
UPDATE ZT_SALES SET DATE_SHIPPED = :TU.DATE_SHIPPED
TOTAL_SALES = :TU.TOTAL_SALES WHERE
Rules and Guidelines for Configuring the Target Update Override:
Use the following rules and guidelines when you enter target update queries:
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.
If you update an individual row in the target table more than once, the SAP table only has data from the last update. If the mapping does not define an order for the result data, running different PowerCenter sessions on identical input data may result in different data in the target table.
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 ZT_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.
Target Table Name:
You can override the target table name in the target definition. Override the target table name when you use a single mapping to load data to different target tables. Enter a table name in the target table name field. You can also enter a parameter or variable.
You can override the target name at the session level. The target table name you set at the session level overrides the target table name you set in the target definition.
EMP_NAME = ‘MIKE SMITH’