Update with a like statement

CloverDX Platform
1

Hello,
Is it possible to use a DBOutputTable component to run an update using a like statement such as in oracle?
(e.g. UPDATE TableName where Field1 like ‘Field1%’)

Thanks for your time!

2

Hi,

if you want to update records that meets a certain condition (LIKE “beginsWith%”) you can write the query into DBOutputTable as follows:

UPDATE tableName SET someCell = $upd where value like 'somePrefix%'

If you would rather pass the prefix from input port, you will have to use DBExecute. This is because DBOutputTable does not expect parameters inside the the condition. For instance the following code would only updates those rows that have value column starting with “$prefix”.

UPDATE tableName SET someCell = $upd where value like '$prefix%'

If you want to use DBExecute in order to use dynamic values as part of a condition, you will need to prepare the query upfront. For such purpose, you may use Reformat component. The CTL code may look like this:

$out.0.query = concat("UPDATE sometable SET value = '", $in.0.upd ,"' WHERE value LIKE '",$in.0.prefix,"%'");

See attached sample. Hope this helps.