Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple rows in sequelize with different conditions

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:

  • ID
  • First Name
  • Last Name
  • Gender
  • Location
  • createdAt

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?

like image 869
proton Avatar asked Apr 04 '18 04:04

proton


People also ask

How do I update multiple rows in 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.

How do I update a row in Sequelize?

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.

How do I insert multiple rows into Sequelize?

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.


2 Answers

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.

like image 59
Vivek Doshi Avatar answered Sep 19 '22 00:09

Vivek Doshi


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
});
like image 29
Mohamed Allal Avatar answered Sep 17 '22 00:09

Mohamed Allal