One Star

Updating records in a table from another table usindg talend

hello all
i am having two tables tab1 and tab2 which are replicate of each other.there are no keys in it .
i update column in tab1 through database and run the job then in tab2 it is not getting updated.
job flow is
toracleinput1>>>tmap>>>toracleoutput1
if i enable traces debug option i am able to see updated row on the output link getting fetched but in database it is not updated.
the newvalue field is having 48 as updated value ,in talend it is geeting fetched but when i check in db it is the same old value...ignore the error sign on toracleoutput..it is some other reason
thanks....

  • Data Integration
16 REPLIES
Moderator

Re: Updating records in a table from another table usindg talend

Hi,
The table 1 is input source table and table 2 is target?
Is there any row is rejected by DB? How did you set your oracle output component? Is there 0 rows/s? Could you please paste your job screenshot into post so that we can get your situation more precisely.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Updating records in a table from another table usindg talend

this is the job yes input table is src and output is target .i just want to udate it....
i used use field option but which should be update key as there are no keys
One Star

Re: Updating records in a table from another table usindg talend

this is the job yes input table is src and output is target .i just want to udate it....
i used use field option but which should be update key as there are no keys
Moderator

Re: Updating records in a table from another table usindg talend

Hi,
You can set a key for your target table so that it can be updated without reduplicated data.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Updating records in a table from another table usindg talend

Hello
the problem is there is no key in both source and target tables.
thanks....
Moderator

Re: Updating records in a table from another table usindg talend

Hi,
Set a key by editing schema of toracleoutput manually.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Updating records in a table from another table usindg talend

thanks for help
but there has to be some unique column in db for setting it as key which is not there all values are duplicate anyways update is not possible in this case .as in talend there must be atleast one key for using update option.
thanks....
Moderator

Re: Updating records in a table from another table usindg talend

Hi,
Could you please elaborate your case with an example with input(all values are duplicate ) and expected output values?
There is also option for "Insert or Update on duplicate key and unique index"
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Updating records in a table from another table usindg talend

Hi,
I am facing the same issue. Even after declaring the column of the target table as key, it is not being updated. Please refer my screenshot.

P.S. - Since I need to update the table, I have chosen 'Default' value for Action on table field and 'Update' for Action on data in my tPostgresqlOutput component.
      
Two Stars

Re: Updating records in a table from another table usindg talend

Everything is ok, but where is the incoming data for your key column. Your tMap doesnot show any incoming records for your key column.
Connect row1.id to id in tmap output.
Thanks
Vaibhav
One Star

Re: Updating records in a table from another table usindg talend

Thanks for your reply.
Even I tried this earlier, but in that case all other column values are overwritten and in DB I find null instead of the old values.
One Star

Re: Updating records in a table from another table usindg talend

I just want an alternative for this query: -
UPDATE transaction_bak
SET 
fl_t_order = tt.t_order
FROM trans_temp tt
WHERE (transaction_bak.id = tt.id)
But I don't know how to do this exactly in talend.
Two Stars

Re: Updating records in a table from another table usindg talend

Please refer to tMap documentation and examples, you will get an idea.
http://www.talendforge.org/tutorials/tutorial.php?idTuto=9
You will have to add lookup table (trans_temp) to tMap and use InnerJoin and join on id for both tables at input side.
Thanks
Vaibhav
One Star

Re: Updating records in a table from another table usindg talend

Yup it worked, thanks a lot Vaibhav San Smiley Happy  
But this is quite strange in talend, even if you want to update a couple of columns out of a hundred odd, you still need to map all the columns and which are not mapped will be updated as null.
Two Stars

Re: Updating records in a table from another table usindg talend

In update query, when there is no "where clause or join condition", then it updates all the records as per the input data. In your case input for all the column is null. That's why it is updating Null... Talend is no more than what we ask him to do. We need to understand properly what we are doing, then things will be as per our expectations.
Thanks
Vaibhav
One Star

Re: Updating records in a table from another table usindg talend

Hello,
I am migrating data from mysql to postgresql database. I have 600 columns in mysql table A.I want to move these values to postgresql table A.Due to the java limit of a method can have at max 65535 bytes its giving error if i do this task in one job ,So I have split the job into two subjobs. First job inserts or updates first 300 columns and Second job updates next 300 columns. But its not working as expected. Its able to insert or update first 300 columns but when the second job finishes its going to overwrite the first 300 column as null.
Is it not possible to update only selected column out of this 600 columns in talend?