Target Update Override - Ways to Update the target without any primary key defined

When we have to perform update on the target table there are 2 ways to perform it :-

1. Using a UPDATE STRATEGY transformation(DD_UPDATE)

2. Configuring Session and defining properties to "Treat all rows as UPDATE"

But both these approach works only when there is a Primary Key defined in the target table. 

The session will fail when there will not be any key defined in the target.

Alternatively, if you want to perform TARGET updates,  with putting condition on COLUMNs not included as PRIMARY KEY in target table, it will not be feasible with out changing the Keys on target table. i.e. If PRIMARY KEY is defined on COLUMN_A in TABLE_A and you want to Update TABLE_A with conditions on or including COLUMN_B

In such cases the UPDATE_OVERRIDE property of TARGET Object can be useful.

• Where to loacte UPDATE_OVERRIDE property?

1. Go to Mapping

2. Double Clink on the concerned TARGET or edit the Target

3. Click on Properties Tab

4. The second Transformation Attribute is the property we are looking for

• Syntax for UPDATE_OVERIDE SQL:

UDATE      <TARGET TABLE>
SET            <COLUMN NAME to be updated> = :TU.<TARGET COLUMN PORT NAME (As in Designer)>   

                   , [Other columns need to be updated]
WHERE      <COLUMN NAME to be treated as KEY > = :TU.<corresponding TARGET COLUMN PORT NAME (As in Designer)>
AND          [other conditions]

• Example: consider u want to update the post and date in employee table based on the name of the employee which is not the primary key in the target.

UPDATE    EMPLOYEE

SET            POST = :TU.POST

                , UPDATE_DATE = :TU.UPDATE_DATE

WHERE EMPLOYEE_NAME = :TU.EMPL_NAME

Following is an example showing Problem statement and step by step Instructions, of two approaches, of suggested solution.

Problem Scenario:

1. Target table has not defined Primary key

  

1. Informatica target object has no Keys - Since the Database table has not Keys the Imported Object in Informatica will not have any Key in the Target definition

2. Original Mapping :  The mapping created using this very Target definition

3. Defining update logic: configuring session for UPDATE Logic 

4. Configure session for Treat All Source Row as "UPDATE"

5. Configure in the session target property to UPDATE as UPDATE - optional

6. The error you will get while execution - the session will fail

Solution:

Approach 1: Using Update in the Target

1. Go to properties tab of Target in Mapping:

2. Edit the "Update Override" attribute - generate SQL and add where Clause with the column name on which you want to update:

3. Please note that you need to remove the very column, used in WHERE clause from the SET Clause list 

4. Go to Workflow and refresh changes in the mapping and save them 

5. Execute the Workflow 

6. Now it has successfully updated the rows:

Approach 2: Forcing Keys in the Target object definition in designer

1. Edit the Target in the target designer

2. Define your Primary key column. Please note that in this case the Database need not have keys defined on the same table:

3. Save the changes 

4. Execute the workflow 

5. The workflow will update the records in the target table: