One Star

Update only columns with a value

Hi there,
new Talend user here. I wonder whether it is possible to perform an update (so configured a tMap followed by a tDBOutput with action update) where only fields that get a new value are updated.
I have found (and succesfully used for something else) the advanced field options to indicate exactly what fields I do and do not want to include in the update.
But now I want a little more, I want the columns only to be included if they actually have a new value from tMap.
Say I have a firstname field, and it has an existing value for some row. Now I have an input source with updates, that may or may not include first names. So in the tMap I have configured a link to the firstname column, but if no value is provided I want to keep the current first name in the table.
Is this possible?
I cannot find options for this. The only alternative I have figured out so far is to join the original source table values with the update input, and in the tMap select either the firstname from the update input, or the original column from the join. But if somebody has an easier solution, please let me know.
Kind regards,
--Sander.
3 REPLIES
Community Manager

Re: Update only columns with a value

Hello
You can add a filter condition on output table of tMap. eg:
row1.firstname!=null||(!row1.firstname.equals(""))
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Update only columns with a value

Hello shong,
thank you for your reply.
I gave the firstname as an example, but in reality I have 10 or so fields that I want to update in the rows, not just firstName. Sorry I did not make that clear. So with your suggestion, I would have to have 10 update outputs, one for each field, and it would lead to 10 actual update statements that may or may not fire depending on availability of data.
What I did not realize when I posted yesterday is that with the advanced option of selecting fields for the update, an update can still be constructed at compile time, something that is not the case for fields that may or may not be updated. So my assumption that this would be an easy extension of what is already there may not be true.
Kind regards,
--Sander.
Community Manager

Re: Update only columns with a value

Hello
So my assumption that this would be an easy extension of what is already there may not be true.

I understand your opinion, but in the code, we need select the original value for each field and check it if it is null or not before update each row, it do so many query and increase the update time.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business