Sequelize bulkCreate updateOnDuplicate update only selected columns-CodePudding

What I am trying to achieve here is that i have a table ProfileMasters with N number of columns, however only 5-6 of those are frequently updated and we want to update those data in bulk and actually we don't want to insert if there is no id matching in the table (couldn't find solution for this)

Hence I came up with the below statement

await ProfileMaster.bulkCreate(itemsForMaster, {updateOnDuplicate: ['id']})

The problem here is the itemsForMaster sends only 5-6 columns that we need to update including the id.

The query generated is below

INSERT INTO `ProfileMasters` (`id`, `type`, `alias`, `reference`, `also_known_as`, `createdAt`, `updatedAt`)
        VALUES(3, 'profile', 'Liver Profile, LFT, liver test', '', '', '2022-09-21 05:37:48', '2022-09-21 05:37:48') ON DUPLICATE KEY UPDATE `id` = VALUES(`id`);

However this doesn't update anything.

Can you please suggest what am I missing here.

CodePudding user response：

It seems like you don't need to use bulkCreate at all because you don't want to insert new records. Unfortunately, there is not such method as bulkUpdate and RDMSes usually don't have bulk UPDATE SQL statements unlike INSERT ones.
So all in all you need to loop though itemsForMaster and call update for each item:

for (const item of itemsForMaster) {
  const { id, ...fieldsToUpdate } = item;
  await ProfileMaster.update(fieldsToUpdate. { id })
}