Unable to update my target table which is used a source table

[sushantk19]

Hi,

I am trying to update 2 fields in my target table (Say table A) with the same Target table used as a source. Now i have just included only 3 fields from Source table (id, field 1, field2) and need to update field 1, field2 in my target table which actually has around 20 fields. basically, i want to patch the data for 2 fields later on after my table has been loaded. 

But when i run the job, it keeps running and doesnt even extract the data from source when source and target tables are the same. Action on Data is "Update" and i have defined id as key in my Talend job. The design is as below

Source Table(A)--->tmap---tlog--->Target Table(A). What could be possible issue?

[sushantk19]

I tested for the same logic by creating another redshift table and it is working fine on that Table(without adding any commit component). I guess the issue is somewhere on DB side. Should I add a index to the table ? Or anything else i can try?

[sushantk19]

because my target table has huge volume of data like 800,000 records.

[manodwhb]

@sushantk19 , do you have index on the table which was working without commit? probably you can check the definition of the tables.

[sushantk19]

I created a temp table as below; Didn't define any index

create table core_dw.temp_order_upd
(
Order_ID INT NOT NULL,
Ordernumber Varchar(255),
ShippingAgent_ID INT,
Brand_ID INT
);

ALTER TABLE CORE_DW.temp_order_upd ADD CONSTRAINT PKTEMPUPDR PRIMARY KEY (Order_ID)

[manodwhb]

@sushantk19 , are you able to load data into temp table without a commit component?

[sushantk19]

@manodwhb : Yes, it is working fine with constraint defined.

[manodwhb]

@sushantk19 , now check the original table structure against temp and you will find the problem

[sushantk19]

temp table is a subset of the Original table defined with 4 fields and same primary key constraint. only difference i could see is the amount of data in my temp table(just 1 record here) and Original table(which has  approx 800,000 records)

[sushantk19]

I truncated the data and reloaded the data. Now the Logic is working fine.