How to update multiple rows in mysql using node js

Question

I am facing problem while updating multiple rows in table.
following is my data

const update_Data = upins_data.map(
                upins_data => [{
                    upin_id: upins_data.upin_id,
                    media_type: upins_data.media_type,
                    land: upins_data.land
                }]
            );

it has multiple values.
i have tired following code

var updateSecTab = `UPDATE tb_bid_upins SET upin_id=?,media_type=?,land=? WHERE bid_id = ?`;
            var query = db.query(updateSecTab,[update_Data,cond],function(error,result,fields){

                if (error) { /**If error while inserting into table 2 it rowback the Transaction */
                    console.log('in err 1');
                    return db.rollback(function () {
                        throw err;
                    });
                }
                db.commit(function (err, result3) {
                    if (err) {
                        console.log('in err 2');
                        return db.rollback(function () {
                            throw err;
                        });
                    }
                    /**Send success result to front end */
                    console.log('success!');
                    res.send(JSON.stringify(result));
                })
            });
            console.log(query.sql);

When i print the query it gives result as follows

UPDATE tb_bid_upins SET upin_id=('[object Object]'), ('[object Object]'),media_type=1,land=? WHERE bid_id = ?

@Molda this is my update data object `[ [ { upin_id: 1, media_type: 5, land: 'Rakhiyal Circle' } ], [ { upin_id: 3, media_type: 6, land: 'Rakhiyal Circle' } ] ]` and this is cond **bid_id=1** — sangRam, Jun 29 '19 at 08:47
I don't think you can do multiple updates this way. See this answer https://stackoverflow.com/a/29519758/3284355 — Molda, Jun 29 '19 at 08:59
I don't know. You can also try this https://stackoverflow.com/a/34866431/3284355 which might be better since it is only single query. — Molda, Jun 29 '19 at 09:08
@Molda thank you for your response.But this two solution are not working for me.Do you have any other solution, i am stuck on this task from 3 days and i dont have much exp in **node.js**. — sangRam, Jun 29 '19 at 09:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/195735/discussion-between-sang-and-molda). — sangRam, Jun 29 '19 at 09:24

score 1 · Accepted Answer · answered Jun 08 '21 at 18:24

Hello everyone i have tried following solution and it works.

var upins_data = [ { upin_id: 1, media_type: 5, land: 'Rakhiyal Circle' } ],
[ { upin_id: 3, media_type: 6, land: 'Rakhiyal Circle' } ]
var cond = 1

i have added above two variable in single array as follows

const update_Data = upins_data.map(
      upins_data => {
        return {
          bid_id: cond,
          upin_id: upins_data.upin_id,
          media_type: upins_data.media_type,
          land: upins_data.land
        }
      }
    );

than i have used foreach on update_Data array and i have created multiple sql quires using mysql foramt function.

var queries = '';
update_Data.forEach(function (item) {
      let sql = mysql.format("UPDATE tb_bid_upins SET upin_id=?,media_type=?,land=? WHERE bid_id = ? ;", [item.upin_id,item.media_type, item.land, item.bid_id]);
      queries += sql;
      console.log(sql);
    });

than i have passed above queries variable in sql query as follows

db.query(queries,(err,result)=>{
   if(err){
      console.log('err',err);
    }else{
      console.log('result',result);
    }    
  });

above code works for me in nodejs .

if anyone wants to improve than they can. thank you

How to update multiple rows in mysql using node js

1 Answers1