Informatica: Target Update Override

Target update override is also like source qualifier override. It is useful to update the target without using the update strategy transformation.

You can find the Target Update Override option in the target definition properties tab. The syntax of update statement to be specified in Target Update Override is

UDATE TARGET_TABLE_NAME

SET TARGET_COLUMN1 = :TU.TARGET_PORT1,

    [Additional update columns]

WHERE TARGET_COLUMN = :TU.TARGET_PORT

AND   [Additional conditions]

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Here TU means target update and used to specify the target ports.                                                                                                   

Target update override can work:

1. Update the target based on non-key columns.

Ex. UPDATE EMPLOYEES SET SALARY = :TU.SAL WHERE EMPLOYEE_NAME = :TU.EMP_NAME

2. Update primary key column of a table

Ex. update TABLE_X set PRIMARY_KEY_COLUMN=:TU.NEW_PRIMARY_KEY_COLUMN where PRIMARY_KEY_COLUMN=:TU.OLD_PRIMARY_KEY_COLUMN;

If using target update override and you also want to update a column in a target table with values of a port that does not exist in the target table: Firstly add a column to the target table definition in the Warehouse Designer. Do not need to define this column in the database. Secondly create the update override with the Temp column with the following example query:

UPDATE ITEM_TARGET SET ITEM = :TU.TEMP_COL WHERE CODE_IN = :TU.CODE_IN AND DIVISION = :TU.DIVISION AND ITEM = :TU.ITEM

Note: using target update override, you must manually put all reserved words in quotes. Configure the session to mark source rows as update. The Target Update option only affects source rows marked as update.