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