Hi,
I need to use a Query Database to update a column in multiple rows in a database table based on the IDs. The IDs are stored in a pv of text type.
For example -
pv has IDs- 14567, 14789
So the 'updatecolumn' column needs to be updated with 0 for both IDs.
TIA!
Discussion posts and replies are publicly visible
As Stewart mentions, the Write to Datastore service is the best way to achieve this. Otherwise, there are 2 options to complete this dynamically with the Query DB node from the old school days.
1) Configure the Query DB node as Multiple Instances over each ID value, then use pv!IDs[tp!instanceindex] in the ac! parameter for your ID value.
2) Utilize the INSTR function in your UPDATE statement to preform multiple updates with one query (as Appian does not allow 'IN'). Such as:
UPDATE tableName SET UpdateColumn = 0 where INSTR(ac!IDs, ',' || tableName.IDs || ',') > 0
Your Node Input for ac!IDs will be set as: ="," & joinarray(pv!IDs, ",") & ","