I'm trying to perform an update command with sequelize on rows in a postgres database. I need to be able to update multiple rows that have different conditions with the same value.
For example, assume I have a user table that contains the following fields:
Assume, I have 4 records in this table, I want to update records with ID - 1 and 4 with a new location say Nigeria.
Something like this: SET field1 = 'foo' WHERE id = 1, SET field1 = 'bar' WHERE id = 2
How can I achieve that with sequelize?
When you need to update the rows using the id , you can change the where option as follows: await User. update( { status: "active" }, { where: { id: [1, 2, 3], }, } ); The update() method is able to update multiple rows with the same update values.
The Model. upsert() method is a new method added in Sequelize v6 that allows you to perform an update statement only when a row with matching values already exist. To update a row, you need to specify the primary key of the row, which is the id column in case of the Users table.
Adding multiple rows at once using Sequelize bulkCreate() method. When you need to insert multiple rows to your SQL database table, you can use the Sequelize bulkCreate() method. The bulkCreate() method allows you to insert multiple records to your database table with a single function call.
You can update multiple record at a time , but same updates for all records , if you want to make different updates for different conditons then you have to run that multiple time
Example :
This will update fields1
to foo , where id
is 1 or 4
let ids = [1,4];
Your_model.update({ field1 : 'foo' },{ where : { id : ids }});
This will update field1
to foo if id
is 1 , and field1
to bar if id
is 4
Your_model.update({ field1 : 'foo' },{ where : { id : 1 }});
Your_model.update({ field1 : 'bar' },{ where : { id : 4 }});
Hope this will clear all your doubts.
You can update multiple rows following your conditions, and to do that the operators are very helpful.
Look here: http://docs.sequelizejs.com/manual/querying.html (operators)
const { Op } = Sequelize;
DisplayMedia.update(
{
field: 'bar'
},
{
where: {
id: {
[Op.in]: [1, 10, 15, ..] // this will update all the records
} // with an id from the list
}
}
)
There is all kinds of operators, including the range operators, or like operator ...etc
Also one of the important questions when it come to update, is how to update all rows?
Not including where
results in an error "Missing where attribute in the options parameter passed to update".
The answer is in the code bellow: provide a where
with an empty object.
await DisplayMediaSequence.update({
default: false
}, {
where: {}, // <-- here
transaction
});
await DisplayMediaSequence.update({
default: true <-- after all turned to false, we now set the new default. (that to show a practical expample) -->
}, {
where: {
id
},
transaction
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With