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: stackoverflow.com/questions/6097815/… ? That's asked more specifically at: stackoverflow.com/questions/47396796/… but no one was able to provide a non-literal approach so far. — Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com, Commented Nov 30, 2021 at 20:58
Your requirement is fundamentally not possible in SQL (of what I know. Also, see here response by the author). Sequelize is only an ORM that converts JavaScript code to SQL. I have required that same condition multiple times, but it's not possible in one query. A better solution sometimes is to remove the data to be updated using Op.in and create it again using bulkCreate. — Rahmat Ali
– Rahmat Ali, Commented Mar 30 at 18:39

Daniel Benedykt · Accepted Answer · 2018-09-10 21:31:27Z

55

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, 2018 at 21:31

Daniel Benedykt

6,57314 gold badges54 silver badges76 bronze badges

answered Apr 4, 2018 at 5:02

Vivek Doshi

58.8k12 gold badges120 silver badges126 bronze badges

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

3 Comments

proton Over a year ago

Yes, it does. Thank you.

Vivek Doshi Over a year ago

@proton , Glad to know, Thanks , Happy Coding BTW.

fahad Over a year ago

@VivekDoshi Is there some way we can do this with a single update query.

Mohamed Allal · Accepted Answer · 2019-04-16 00:15:13Z

21

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

edited Apr 16, 2019 at 0:15

answered Mar 28, 2019 at 0:19

Mohamed Allal

21.4k6 gold badges107 silver badges110 bronze badges

4 Comments

Sgnl Over a year ago

IMO this should be the accepted answer, it directly solves the question asked. Thank You

Rahmat Ali Mar 30 at 18:28

@Sgnl it's not even close to solving the OP question. This answer will update the field with the value of bar for all given ids. While, OP asked to update field with foo when id = 1 and field with bar when id = 2.

Mohamed Allal Apr 4 at 17:03

@RahmatAli Good point there, my answer would require running multiple queries for different values. And i guess the only possible way to do it in sql (postgres, mysql) if i'm not wrong is by using CASE, WHEN syntax. And for that what would comes to mind is Sequelize.literal() with case when inside. I'll test it out first and i will update the answer after. And thanks good point.

Rahmat Ali Apr 5 at 16:42

@MohamedAllal I don't have in-depth knowledge of databases, so I can't say anything about CASE WHEN. Many times, I need to bulk update many fields. What's best and fastest works for me is removing the updated fields using Op.in and creating new records using bulkCreate(). Of course, it does not work everywhere, like if you have foreign keys associated with those records or you are removing, creating a lot so the limit of primary key will reach soon. But, most of the time, it works and is the fastest solution with the smallest number of database calls.

Justin Joseph · Accepted Answer · 2021-06-01 18:18:47Z

3

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

answered Jun 1, 2021 at 18:18

Justin Joseph

4,0292 gold badges15 silver badges16 bronze badges

Collectives™ on Stack Overflow

Update multiple rows in sequelize with different conditions

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related