How to update a column value of multiple records in a table?

I would suggest querying for the rows of table A by IDs. Then use updatecdt() to populate column X with the value, and then use an a!writeToDataStoreEntity() to write it back to the database.

If this is a one time change, you could do so using update scripts on your database directly. If it is a more complicated use-case, you could do the same through a process model as well, but I would suggest working directly on the DB level or on an interface.

what is updatecdt() function? is it a plugin in app market?

Please refer to CDT Manipulation plug-in.

I have tested with UpdateCDT. The column X we need to update is a list of numbers. Which means we need to append values than overwrite. The UpdateCDT is overwriting values but not appending. Is there a way to append? Also, as shown in below expression i tested, the value i need to save against each column is same, but i have to repeat it in the SourceIds array for the count of records i am trying to update. Is there a better way of doing it?

updatecdt(rule!GetTechnologyByIdMultiple(ri!ids),{Id: ri!ids, SourceIds: {2,2}})

You can use fn!repeat or a!forEach function. SourceIds:fn!repeat(length(ri!ids),2)

Thank You.

how to append the new value 2 to existing array of values in that column? assuming the column has a value of {1,2,3} i need to append 2 value for each record in that column X and the result should be {1,2,3,2} as column value for each record i update. How to achieve this?

Is it possible to append with UpdateCDT() ?

I just double-checked, and can confirm that updateCDT() requires the update property arrays to be the same length as the original CDT array.

My suggestion is you do your querying / appending / updating in a step-by-step approach, using local variables for each step, to allow you to get each step working one at a time, and test each step.

 

/* original code: */
/* updatecdt(rule!GetTechnologyByIdMultiple(ri!ids),{Id: ri!ids, SourceIds: {2,2}}) */


/* my suggestion: */
with(

  /* initial query */
  local!initialQuery: rule!GetTechnologyByIdMultiple(ri!ids),
  
  /* now add the new entry.  you may or may not need to typecast it to your CDT type, I'm not sure. */
  local!appendedVersion: append(
    local!initialQuery,
    type!TechnologyCDT(
      Id: 2,
      SourceIds: 2
    )
  ),
  
  /* with UpdateCDT(), you only update the values that need updating - the rest stay untouched */
  local!updatedCdt: updateCdt(
    local!appendedVersion,
    {
      SourceIds: a!forEach(local!appendedVersion, 2) /* outputs "2" for every entry.  same as "repeat()" function but probably safer. */
    }
  ),
  
  
  /* finally, display the resulting value: */
  local!updatedCdt
)

I just double-checked, and can confirm that updateCDT() requires the update property arrays to be the same length as the original CDT array.

My suggestion is you do your querying / appending / updating in a step-by-step approach, using local variables for each step, to allow you to get each step working one at a time, and test each step.

 

/* original code: */
/* updatecdt(rule!GetTechnologyByIdMultiple(ri!ids),{Id: ri!ids, SourceIds: {2,2}}) */


/* my suggestion: */
with(

  /* initial query */
  local!initialQuery: rule!GetTechnologyByIdMultiple(ri!ids),
  
  /* now add the new entry.  you may or may not need to typecast it to your CDT type, I'm not sure. */
  local!appendedVersion: append(
    local!initialQuery,
    type!TechnologyCDT(
      Id: 2,
      SourceIds: 2
    )
  ),
  
  /* with UpdateCDT(), you only update the values that need updating - the rest stay untouched */
  local!updatedCdt: updateCdt(
    local!appendedVersion,
    {
      SourceIds: a!forEach(local!appendedVersion, 2) /* outputs "2" for every entry.  same as "repeat()" function but probably safer. */
    }
  ),
  
  
  /* finally, display the resulting value: */
  local!updatedCdt
)