Using raw bulk update query with Sequelize

Question

I would like to use this type of request with sequelize to make large number of updates in one request (for performance reasons) :

UPDATE employee
SET address = new.address,
name = new.name
from (values :updateStack) AS new(address, name, employeeId)
WHERE employee.id = new.employeeId

Here is the value of updateStack :

[{
    address: 'France',
    name: 'Chris',
    employeeId: 21
}, {
    address: 'UK',
    name: 'Steve',
    employeeId: 42
}]

I'm not sure how sequelize can properly parse the updateStack array. Any idea ?

This SQL query is working fine :

UPDATE employee
SET address = new.address,
name = new.name
from (values ('France', 'Chris', 21), ('UK', 'Steve', 42)) AS new(address, name, employeeId)
WHERE employee.id = new.employeeId

Thank you and have a good day.

In the docs, there is an example of bulk updating.. docs.sequelizejs.com/manual/… — Rastalamm
– Rastalamm, Commented Jun 28, 2019 at 14:51

waloc · Accepted Answer · 2019-06-30 20:36:27Z

6

I've discovered how to do it !

sequelize.query(
            `
                UPDATE employee
                SET address = new.address,
                name = new.name
                from (values ?) AS new(address, name, employeeId)
                WHERE employee.id = new.employeeId
            `,
            {
                replacements: [['France', 'Chris', 21], ['UK', 'Steve', 42]],
                type: models.models.sequelize.QueryTypes.INSERT
            }
    )

answered Jun 30, 2019 at 20:36

waloc

811 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philipp Kyeck Over a year ago

which DB are you using?

Collectives™ on Stack Overflow

Using raw bulk update query with Sequelize

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related