how to bulk update using knexjs

Question

I'm trying to batch update table users that contains these columns id (primary key) , status (text) , active (numeric).

the array i'm receiving from back-end is something like this:

[ 
     { id: 33715, status: 'online', active: 10 },
     { id: 39129, status: 'offline', active: 0.1 },
     { id: 36090, status: 'loggedin', active: 24 },
     { id: 34452, status: 'loggedout', active: 1 },
]

active is time in hours. now i want to bulk update this array into users table. as each object represents a row in a table.

I've tried this approach according to this solution Patrick Motard

function bulkUpdate (records) {
  var updateQuery = [
      'INSERT INTO users (id, status, active) VALUES',
      _.map(records, () => '(?)').join(','),
      'ON DUPLICATE KEY UPDATE',
      'status = VALUES(status),',
      'active = VALUES(active)'
  ].join(' '),

  vals = [];
  _(records).map(record => {
    vals.push(_(record).values());
  });
  return knex.raw(updateQuery, vals)
  .catch(err => {
    console.log(err)
  });
}
bulkUpdate(response);

but i get this error

error: syntax error at or near "DUPLICATE"

so what i'm missing here. and does anyone by chance have a better solution without using promises or bluebird then do trx.commit , this consumes large cpu and ram. and doesn't do the purpose of update 10,000 row at once

score 4 · Accepted Answer · answered Aug 27 '19 at 16:13

I don't see any ON DUPLICATE KEY UPDATE reference in

https://www.postgresql.org/docs/9.5/sql-insert.html

You could try out ON CONFLICT DO UPDATE SET. Try out first with plain SQL to write a query that seems to work correctly and then it is easy to convert to javascript generated one.

how to bulk update using knexjs

1 Answers1