One Star

Insert or update on duplicate key but not update all fields

I am trying to load data into a dimension table. It is straight forward enough, if the record does not exist, created it. If the record does exist update it. Setting "Action on data" to "Insert or update on duplicate key or unique index" in my tMysqlOutput step does basically what I am looking for. However, when updating, I do not want to update all fields. There are couple that I want to keep as the current value while updating all the other fields. It is not clear to me how I can set the action at the individual field level. Any help would be greatly appreciated.
Thanks,
Jason
17 REPLIES
One Star

Re: Insert or update on duplicate key but not update all fields

You can do it using repective database level SCD compoent (example: tOracleSCD). What you are trying to achieve is combining type 1 and type 0 SCD .
You can find the help in TalendIntegrationSuite_Components document and look for tMySQLSCD component.
Seven Stars

Re: Insert or update on duplicate key but not update all fields

Just exclude the fields you don't want updated from the schema of tMysqlOutput or use the "Field options" in the Advanced Settings.
One Star

Re: Insert or update on duplicate key but not update all fields

alevy, this was exactly what I was looking for. Thanks for helping out this newbie.
One Star

Re: Insert or update on duplicate key but not update all fields

Let?s move on a little further with the point.
In my case there are two fields to be updated on duplicate key (unique key column name - `code`) - `name`, `date_modified`. On initial insert `date_modified` is equal to `date_created`. And I would like to modify `date_modified` only in case if `name` value is really modified. Every time the job is executed `date_modified` column should remain unchanged if value of the `name` column is the same as before updating.
It looks like database BEFORE UPDATE trigger could help. But I don?t want to split business logic between TOS and database. Moreover in my project this procedure should be re-used against many other database tables. So creating trigger for each table won?t be elegant solution.
Any ideas which component (or its options) can help?
Seven Stars

Re: Insert or update on duplicate key but not update all fields

I don't believe there is a "nice" way to do this. You have only one option I can see: first read the data from the table to compare with your "new" data in tMap and send only the records with changed 'name' values to the DB.
It might also be possible to use a tJavaFlex to introduce additional conditions into the prepared statements that Talend creates but this would be tricky and very much non-standard Smiley Happy.
Seven Stars

Re: Insert or update on duplicate key but not update all fields

Another thought: perhaps you could use a tRow component with your own PreparedStatement instead of tOutput.
One Star

Re: Insert or update on duplicate key but not update all fields

Thank you, alevy, for your answers!
Since posted my question I played a little with different approaches. Usage of tRow component is described in this book http://www.packtpub.com/getting-started-with-talend-open-studio-for-data-integration/book . I think finally I will choose this approach as it lets operate with simple customizable SQL query.
But before I tried to find a solution within tMap component. I used filters in output checking equality between "new" and "old" values. While filters itself solves the problem perfectly I can't manage updating tables: neither different options of "Action on data", nor "Use field options? in ?Advanced Settings? of tMysqlOutput component let me to update values. I tried to debug result SQL queries with tJavaRow component but unfortunately it returns only null values for statement like System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY")));
Seven Stars

Re: Insert or update on duplicate key but not update all fields

No, tMysqlOutput_1_QUERY is never populated. What problem are you having with updating the table?
One Star

Re: Insert or update on duplicate key but not update all fields

Thanks again, alevy, for your interest to my case and helping me.
Here are some screenshots I made for the job explanation. As you can see tMap filters work as it should be. But as the job finishes the `regions` table is not updated with new value of `name` field. That?s the main problem.



The_Job

tMap_Editor

tMysqlOutput_Advanced_Settings

tMysqlOutput_Basic_Settings

tMysqlOutput_Schema
One Star

Re: Insert or update on duplicate key but not update all fields

P.S. Sorry, I'm new on inserting images here. Sorry again...
P.P.S. BTW, are there any guides how to insert those "beautiful" screenshots right in the messages? I can't manage to find any of these.
Moderator

Re: Insert or update on duplicate key but not update all fields

Hi erop,
If you register and log in as a Community member, you'll get a Image upload box that allows to upload screen captures and images up to 200KB.
For registered users, I realized that the image upload feature is only available when clicking on "post reply" (not through the quick post").

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: Insert or update on duplicate key but not update all fields

Many thanks, Sabrina! I swear I'll never use "Quick post" option Smiley Happy
Seven Stars

Re: Insert or update on duplicate key but not update all fields

You've specified the update key as code_federal but you are not populating that field in the tMap!
One Star

Re: Insert or update on duplicate key but not update all fields

You've specified the update key as code_federal but you are not populating that field in the tMap!

Thank you, alevy, so much! As usual for me, the solution was so obvious and simple Smiley Happy
One Star

Re: Insert or update on duplicate key but not update all fields

No, tMysqlOutput_1_QUERY is never populated.

alevy, have you any ideas about approaches on debugging/logging resulting SQL query in tMysqlOutput component?
Seven Stars

Re: Insert or update on duplicate key but not update all fields

I've seen the question asked a few times on the forum but I don't think there's a way to do it in Talend since it uses prepared statements. You would have to look at the DB logs. Having said that, you can certainly at least see the data you're sending to tMysqlOutput with a tLogRow.
One Star

Re: Insert or update on duplicate key but not update all fields

Thank you, alevy!