Bulk Update in CAP (Node.js)

[KM11]

Hi CAP Experts

david.kunz2 gregorwolf mariusobert thomas.jung mioyasutake

I am trying to do a bulk update on an entity but getting an error "invalid column name: 0_ID".

The entity has only one key field CUID from sap/cds/common.

Update statement: "await db.run(UPDATE(Rates).with(arrUpdate))".

arrUpdate is an array of json objects. In all the json objects I am passing the ID along with other fields to be updated.

Expected behaviour: above query shall update all fields where respective ID exists in the DB table. Or do I need to update each record in a loop?

PS: It is not an update on a deep composition.

Thanks Kanika

[martinstenzig]

Can you do a console.log on Rates and arrUpdate?

I bet you have some weird situation where your Rates object or the array have incorrect information.

[martinstenzig]

It turns out, Update does not seem to be able to handle arrays as input at the moment.

There is an easy fix for you though, simply wrap it in a for loop like this..

        for (let upd of arrUpdate) {
            await db.run(UPDATE(Rates).with(upd))
        }

[KM11]

Thanks Martin. Was just trying to make a bulk update to avoid multiple hits on DB .

But, I think as of yet it is not possible to do a bulk update, atleast in node.js stack.

More answers from the community welcome 😉

Thanks

Kanika

[CronJorian]

Just as a remark: This slows down the async process way too much, as you wait for each Promise to resolve before updating the next one.

Instead of:

        for (let upd of arrUpdate) {
            await db.run(UPDATE(Rates).with(upd))
        }

 Do:

let promises = []
for (let upd of arrUpdate) {
    promises.push(db.run(UPDATE(Rates).with(upd)))
}
await Promise.all(promises)

[KM11]

Hi martin.stenzig3

Below are the console.log output of both arrUpdate and Rates(entity).

Also, is my update statement correct as per the syntax?

definition of Rates: const { Rates } = cds.entities(<namespace>);

arrUpdate(SimpleArray):

"console.log(arrUpdate)" 
[
  {
    ID: 'f62ea6ec-9c53-4159-85db-8b235b4b0ffd',
    prdno: '123456',
    startDate: '2022-07-14',
    endDate: '2022-07-14',
    value: '1.0000',
    comments: 'New Model Rate'
  },
  {
    ID: 'd0473d3d-25eb-499c-a212-d4b32ecaeaa8',
    prdno: '123457',
    startDate: '2022-07-14',
    endDate: '2022-07-14',
    value: '2.0000',
    comments: 'New Model Rate'
  },
  {
    ID: 'e3b7428d-2005-4ce3-93f1-555396066472',
    prdno: '123458',
    startDate: '2022-07-14',
    endDate: '2022-07-14',
    value: '3.0000',
    comments: 'New Model Rate'
  },
  {
    ID: 'daf419dc-8a54-4f3c-8f68-7be9e14a333f',
    prdno: '123459',
    startDate: '2022-07-14',
    endDate: '2022-07-14',
    value: '4.0000',
    comments: 'New Model Rate'
  },
  {
    ID: 'e62b5b1a-66d7-412a-81c7-fc689486b0e6',
    prdno: '123455',
    startDate: '2022-07-14',
    endDate: '2022-07-14',
    value: '5.0000',
    comments: 'New Model Rate'
  }
]

Rates(entity):

"console.log(Rates)"

entity {
kind: 'entity',
includes: [ 'cuid', 'managed' ],
elements: [Object: null prototype] {
ID: string { key: true, type: 'cds.UUID' },
createdAt: date {
'@cds.on.insert': { '=': '$now' },
'@UI.HiddenFilter': true,
'@Core.Immutable': true,
'@readonly': true,
'@odata.on.insert': { '#': 'now' },
type: 'cds.Timestamp',
'@Core.Computed': true,
'@Common.Label': '{i18n>CreatedAt}'
},
createdBy: string {
'@cds.on.insert': { '=': '$user' },
'@UI.HiddenFilter': true,
'@Core.Immutable': true,
'@readonly': true,
'@odata.on.insert': { '#': 'user' },
type: 'cds.String',
length: 255,
'@Core.Computed': true,
'@Common.Label': '{i18n>CreatedBy}',
'@Core.Description': '{i18n>UserID.Description}'
},
modifiedAt: date {
'@cds.on.insert': { '=': '$now' },
'@cds.on.update': { '=': '$now' },
'@UI.HiddenFilter': true,
'@readonly': true,
'@odata.on.update': { '#': 'now' },
type: 'cds.Timestamp',
'@Core.Computed': true,
'@Common.Label': '{i18n>ChangedAt}'
},
modifiedBy: string {
'@cds.on.insert': { '=': '$user' },
'@cds.on.update': { '=': '$user' },
'@UI.HiddenFilter': true,
'@readonly': true,
'@odata.on.update': { '#': 'user' },
type: 'cds.String',
length: 255,
'@Core.Computed': true,
'@Common.Label': '{i18n>ChangedBy}',
'@Core.Description': '{i18n>UserID.Description}'
},
prdno: string { type: 'cds.String', length: 6 },
startDate: date { type: 'cds.Date' },
endDate: date { type: 'cds.Date' },
value: number { type: 'cds.Decimal', precision: 5, scale: 4 },
comments: string { type: 'cds.String', length: 3000 }
}
}

Thanks

Kanika