Skip to main content

Data & Intelligence

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]

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

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. 

 

 

 

Thoughts on “Informatica: Target Update Override”

  1. In the above 2nd scenario…
    What is NEW_PRIMARY_KEY_COLUMN and OLD_PRIMARY_KEY_COLUMN where are these columns coming from? Are they present in the target table. Because i want to load data which is coming from some other source table.

  2. You need to add a column to the target table definition in the Informatica Power Designer. But do not need to define this column in the database. And in mapping you could point the port of new ID value to this NEW column (NEW_PRIMARY_KEY_COLUMN), and point the port of old id value to the original ID column(OLD_PRIMARY_KEY_COLUMN). Then the target update override will work.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.