[resolved] conditional update: update db column or keep original value on test

One Star

[resolved] conditional update: update db column or keep original value on test

Is it possible to have a tMap conditionally update a mapped destination table?
I have this case: a data file listing foreign keys whose records must have some column (responsemethodid) with existing data updated (to the value 7). If the ID doesn't exist in the data file, the preexisting record's responsemethodid must remain unchanged. The way that works for me is to make a tMap inner-join the data file, so the update only executes against the matching records.
There is another subjob that also updates other columns on the same destination table, and in this some of the same and many other records are updated (but not the responsemethodid column).
I am looking to consolidate these two process into a single update. When it comes to the responsemethodid, it would be slick to be able to map an expression something like:
row3.bcsummsid.isNull ? 7 : destinationtable.responsemethodid; --> responsemethodid
to solve it, but not sure what the syntax of referencing the current value is.
I spent a good 45min on the phone with a talented talend rep yesterday and was all jazzed about making one of my processes more efficient, but when I set out to do it on my own, I lost some steam/knowledge. Apologies to us both for not having taken better notes-- the tool just makes me feel confident, I guess Smiley Wink

Accepted Solutions
One Star

Re: [resolved] conditional update: update db column or keep original value on test

Check it out (see attached), it works! Here I am using the same table I am using for updates also for lookups, thereby allowing me to conditionally update or keep a given value whilst updating a bunch of other columns.
I check for null in the Exclusions join, and if there is no null (meaning the record ID was found in the exclusions file), I update with a constant value of 7, otherwise, I keep the original value (provided by the table as a lookup):
ExceptionsList.bcsummsid != null ? 7 : SummonsReference.responsemethodid

All Replies
One Star

Re: [resolved] conditional update: update db column or keep original value on test

note: in SQL this would translate to something like:
update tablename set(field1 = row3.field1, field2=row3.field2, responsemethodid = 7) // setting value=7
or
update tablename set(field1 = row3.field1, field2=row3.field2, responsemethodid = responsemethodid) // unchanging in other cases
..and now that I write it out, I see it would likely have to be a feature of tMap to generate eval SQL to _conditionally_ build "responsemethodid" or "7" as SQL generated... doesn't seem likely.
One Star

Re: [resolved] conditional update: update db column or keep original value on test

One way to solve this is to have two outputs from your tMap, one that is updating the responseMethodID, and one that is not (simply removed from the schema that you are updating). You can create a filter in tMap to filter out the records where row3.bcsummsid == null (use the first arrow button on the target side of tMap to enable the filter expression) you will have to use the rejects button (second button) to capture the delta.

hth,

Thomas
One Star

Re: [resolved] conditional update: update db column or keep original value on test

Thomas-
Thanks for the reply. Updating responsemethodid and the other columns by the other process are mutually exclusive: not having to update one does not imply not having to update the others. I think what you are describing is either still a two-step update (assuming the update to "the other columns" happens at some point) or is only updating the "other columns" when responsemethodid needs to be updated.. I may be wrong?
Another idea occurred to me: maybe the way to do it is to tMap in the destination table as a lookup and pick the responsemethodid from Exclusions lookup when necessary and orig tablename otherwise.
responsefile --> tMap (main:responsefile, L:Exclusions, L:destTablename) --> destTablename
I may give this a shot...
One Star

Re: [resolved] conditional update: update db column or keep original value on test

Check it out (see attached), it works! Here I am using the same table I am using for updates also for lookups, thereby allowing me to conditionally update or keep a given value whilst updating a bunch of other columns.
I check for null in the Exclusions join, and if there is no null (meaning the record ID was found in the exclusions file), I update with a constant value of 7, otherwise, I keep the original value (provided by the table as a lookup):
ExceptionsList.bcsummsid != null ? 7 : SummonsReference.responsemethodid

Re: [resolved] conditional update: update db column or keep original value on test

I have to say, bronius' solution was very helpful, and even somewhat elegant given the constraints within the Talend tMap.