Question Re: Bulk Update in CAP (Node.js) in Technology Q&A

Bulk Update in CAP (Node.js)

KM11 — Fri, 08 Jul 2022 17:52:00 GMT

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

Re: Bulk Update in CAP (Node.js)

martinstenzig — Fri, 08 Jul 2022 18:01:54 GMT

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.

Re: Bulk Update in CAP (Node.js)

KM11 — Sat, 09 Jul 2022 08:20:41 GMT

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

Re: Bulk Update in CAP (Node.js)

martinstenzig — Sun, 10 Jul 2022 10:08:59 GMT

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))
        }

Re: Bulk Update in CAP (Node.js)

KM11 — Mon, 11 Jul 2022 05:48:06 GMT

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

Re: Bulk Update in CAP (Node.js)

CronJorian — Wed, 20 Nov 2024 08:12:41 GMT

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)