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
SET TARGET_COLUMN1 = :TU.TARGET_PORT1,
[Additional update columns]
WHERE TARGET_COLUMN = :TU.TARGET_PORT
AND [Additional conditions]
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.