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

[Anonymous]

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?

[Anonymous]

Hi,
What's your target DB? Are you looking for Action on data option " Update or Insert "?
Best regards
Sabrina

[Anonymous]

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"?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi Laurent,
I was able to understand your explanation till the inner join part but then I was lost 
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.

[Anonymous · Accepted solution]

could be something like :

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


regards
laurent

[Anonymous]

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

[Anonymous]

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