Insert or update on duplicate key but not update all fields

[Anonymous]

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

[Anonymous]

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.

[alevy]

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

[Anonymous]

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

[Anonymous]

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?

[alevy]

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 .

[alevy]

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

[Anonymous]

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

[alevy]

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

[Anonymous]

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