Seven Stars

[resolved] Update field values based on Update or insert status

Hi,
This "probably" is not a Talend DI issue but I am not able to figure out how to do it so I am asking here.
I have to copy data from 1 database table to another. In the destination table there are 2 fields created_date and updated_date.
The value in these fields is to be populated, as the name suggests, when the record is created and updated respectively.
So when loading a record from the source into the destination I need to check (based on the destination's primary key) if the record already exists in the destination or not. If it does not then I need to set the value of the created_date field. If the record exists (id is already present) then I need to set the value of the updated_date field and keep the original value of the created_date field.
Now my problem is how and where do I make this check and set the appropriate field's value?
1 ACCEPTED SOLUTION

Accepted Solutions
Seventeen Stars

Re: [resolved] Update field values based on Update or insert status

could be something like :

the tip is to use "the catch inner join rejet" for the create action.


regards
laurent
14 REPLIES
Moderator

Re: [resolved] Update field values based on Update or insert status

Hi,
What's your target DB? Are you looking for Action on data option " Update or Insert "?
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.
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Hi,
My target is a Mysql database table.
But what I want to know is by using the "Action on data" option of "Update or insert" how can I ensure that I set the created_date value field if the record is going to be "inserted" and set updated_date value if the record is going to be "updated"?
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Do you think that writing a database trigger for this update is an easier option than getting it done through Talend?
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Does nobody have a solution for my problem? Or an opinion whether using database triggers is a better option?
Seventeen Stars

Re: [resolved] Update field values based on Update or insert status

hi all,
use a tMap to inner join on your ID.
Use a flow output to update items and another one to create items.
your update flow is the output flow where id is found (join).
add inner join rejet option for your creates items flow.
Use create action for create flow, and update action on table for the other one.
Manage update thanks advanced setting and 'use field options'.
have a look at talend help for some scenario :
https://help.talend.com/search/all?query=tMysqlOutput&content-lang=en

hope it helps
regards
laurent
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Hi Laurent,
I was able to understand your explanation till the inner join part but then I was lost Smiley Sad
Do you have some screenshots to help me understand?
I have designed an alternative approach to this where I do the following:

put the source and destination table as tMysqlInput components
link them to a tMap component with the source as the main link and destination table as lookup
make a left outer join between these tables in the tMap based on the key field
declare a boolean variable "insert_record" and set it's value as (destination_table.key_field == null)
In the output row, to the same destination table as a tMysqlOutput component, I set the value of created_date with the following expression Var.insert_record ? new Date() : destination_table.created_date
Similarly I set the value of updated_date field with the expression !Var.insert_record ? new Date() : null

See the above screenshot where I have done the above described mapping.
Do you think this is a good idea?
Also please share your idea with some screenshots (if possible) so that I can try the same.
Seventeen Stars

Re: [resolved] Update field values based on Update or insert status

could be something like :

the tip is to use "the catch inner join rejet" for the create action.


regards
laurent
One Star

Re: [resolved] Update field values based on Update or insert status

But what I want to know is by using the "Action on data" option of "Update or insert" how can I ensure that I set the created_date value field if the record is going to be "inserted" and set updated_date value if the record is going to be "updated"?

Hi,
Do you try to just play with "Field options" of the tMySQLOutput :
Your tMySQLOutput is design as "Insert or Update" so it will automatically insert or update your output table.
In "Fields Options" :
- For your field created_date, check insertable and uncheck updatable ==> So, this field will be populated for an insert and it will not be updated for an update
- For your field updated_date, uncheck insertable and check updatable ==> So, this field will not be populated for an insert and will be updated for an update
Seventeen Stars

Re: [resolved] Update field values based on Update or insert status

for information "Insert or Update" is not a optimize solution (haven't got benchmark result by my side but it 's slower).
regards
laurent
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Thanks Laurent.
Your approach seems to work for me.
Seventeen Stars

Re: [resolved] Update field values based on Update or insert status

thanks to put topic as 'resolved'
regards
laurent
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Hi,
I have got one more question related to this. Is there a way to know how many records have been inserted or updated?
A log or a message that can be printed on the console after the job execution is completed.
One Star

Re: [resolved] Update field values based on Update or insert status

Yes, you have some variables linked to your tMysqlOutput component :
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_DELETED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_REJECTED"))
Seven Stars

Re: [resolved] Update field values based on Update or insert status

Thanks tangiobs!