What is Target Update Override? What is the Use ?

Question

i have taken target table as my lookup table using static cache.in source i have duplicate values.

in mapping i ahve used update strategy transformation but not i m not able update data in target table

Example:Initially(i mean after session Load)

source table Lookup table Target table

ID    Name           ID    Name            ID    Name    
1     A              1     A               1     A 
2     B                                    2     B

Now im inserting two more records

3      C
1      E

but its not updating below record

1    E

i am getting below error

One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "TABLE_NAME" from having duplicate values for the index key.

I know if i use dynamic lookup it will work correctly but in static. please give the reason ASAP.

Answer 1

When you used an update strategy transformation in the mapping or specified the "Treat Source Rows As" option as update, informatica integration service updates the row in the target table whenever there is match of primary key in the target table found.

The update strategy works only

when there is primary key defined in the target definition.
When you want update the target table based on the primary key.

What if you want to update the target table by a matching column other than the primary key? In this case the update strategy wont work. Informatica provides feature, "Target Update Override", to update even on the columns that are not primary key.

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]

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

Example:

Consider the employees table as an example. In the employees table, the primary key is employee_id. Let say we want to update the salary of the employees whose employee name is MARK. In this case we have to use the target update override. The update statement to be specified is

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