Pass in an array of values to update multiple rows via one raw query

Question

I'm using a raw query with sequelize to update multiple rows with one query in my postgres DB. For example:

update customers_table as c set
    name = c2.name
from (values
    (66, 'Joe'),
    (70, 'Ann')
) as c2(id, name)
where c2.id = c.id;

In this example, two rows with the ids 66 and 70 will have their names updated.

But how do I pass in data to be updated as an array in nodejs via sequelizejs raw query? For example (this doesn't work, but I'm trying to get at what it is I'm trying to do):

const arrayOfData = [[66, 'Joe'], [70, 'Ann']];

const query = 
`update customers_table as c set
    name = c2.name
from (values
    ${arrayOfData}
) as c2(id, name)
where c2.id = c.id;`;

const update = sequelize.query(query).spread((results, metadata) => {
    return results;
});

And one more thing: shouldn't your arrayOfData format would be something like this: const arrayOfData = [[66, 'Joe'], [70, 'Ann']]; — Alireza Ahmadi
– Alireza Ahmadi, Commented Feb 24, 2019 at 8:06

Alireza Ahmadi · Accepted Answer · 2019-02-24 07:43:31Z

3

If number of elements in arrayOfData are constant, you can use this syntax:

db.query('update customers_table as c set
    name = c2.name
from (values
    (?),(?)
) as c2(id, name)
where c2.id = c.id', { replacements: arrayOfData })

Otherwise, you can simply use the following snippet:

const values = arrayOfData.map(row =>  `(${row.join(',')})`).join(',')
db.query(`update customers_table as c set
    name = c2.name
from (values
    ${values}
) as c2(id, name)
where c2.id = c.id`)

You can learn more about Sequelize raw query from its official documentation

answered Feb 24, 2019 at 7:43

Alireza Ahmadi

1,56118 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Pass in an array of values to update multiple rows via one raw query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related