Update multiple rows in sequelize with different conditions

Question

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?

Does any DBMS support that SQL syntax? Maybe what you want is `CASE WHEN` as mentioned at: https://stackoverflow.com/questions/6097815/using-a-conditional-update-statement-in-sql ? That's asked more specifically at: https://stackoverflow.com/questions/47396796/how-to-use-case-when-expression-in-sequelize but no one was able to provide a non-literal approach so far. — Ciro Santilli OurBigBook.com, Nov 30 '21 at 20:58

score 51 · Accepted Answer · edited Sep 10 '18 at 21:31

51

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.

edited Sep 10 '18 at 21:31

Daniel Benedykt

6,496
12
51
73

answered Apr 04 '18 at 05:02

Vivek Doshi

56,649
12
110
122

1

Yes, it does. Thank you. – proton Apr 04 '18 at 05:08
@proton , Glad to know, Thanks , Happy Coding BTW. – Vivek Doshi Apr 04 '18 at 05:12
@VivekDoshi Is there some way we can do this with a single update query. – fahad Jan 14 '21 at 16:27

Mohamed Allal · Answer 2 · 2019-04-16T00:15:13.930

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
});

IMO this should be the accepted answer, it directly solves the question asked. Thank You — Sgnl, Jul 29 '20 at 02:39

score 3 · Answer 3 · answered Jun 01 '21 at 18:18

we planning to save different values for same fields in multiple row there is a possible for getting all field values are same in database. using for loop

const {idArray,group_id} = params;

for(const item of idArray){

    const response = await Your_model.findOne({ where:{group_id,user_id:null}, order: [['id', 'DESC']] });

    await response.update({user_id:item});
}

Update multiple rows in sequelize with different conditions

3 Answers3

Linked