topic Re: Insert or update on duplicate key but not update all fields in Talend Studio

Insert or update on duplicate key but not update all fields

Anonymous — Thu, 26 Jul 2012 20:13:39 GMT

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

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

Anonymous — Thu, 26 Jul 2012 21:37:14 GMT

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.

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

alevy — Fri, 27 Jul 2012 03:51:05 GMT

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

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

Anonymous — Fri, 27 Jul 2012 09:06:54 GMT

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

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

Anonymous — Fri, 22 Mar 2013 19:22:35 GMT

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?

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

alevy — Wed, 03 Apr 2013 03:07:46 GMT

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 .

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

alevy — Mon, 08 Apr 2013 02:36:24 GMT

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

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

Anonymous — Mon, 08 Apr 2013 06:48:32 GMT

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")));

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

alevy — Mon, 08 Apr 2013 07:01:44 GMT

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

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

Anonymous — Mon, 08 Apr 2013 10:19:06 GMT

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

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

Anonymous — Mon, 08 Apr 2013 10:20:15 GMT

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.

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

Anonymous — Mon, 08 Apr 2013 10:30:50 GMT

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

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

Anonymous — Mon, 08 Apr 2013 10:36:26 GMT

Many thanks, Sabrina! I swear I'll never use "Quick post" option

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

alevy — Tue, 09 Apr 2013 00:14:41 GMT

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

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

Anonymous — Tue, 09 Apr 2013 08:19:03 GMT

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

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

Anonymous — Tue, 09 Apr 2013 12:31:28 GMT

No, tMysqlOutput_1_QUERY is never populated.

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

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

alevy — Wed, 10 Apr 2013 00:08:46 GMT

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.

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

Anonymous — Wed, 10 Apr 2013 07:04:35 GMT

Thank you, alevy!