Update multiple records with mysql and nodeJs

Question

I am trying to update multiple records that meet specific condition using NodeJS and Sequelize. Example,if student_id is 1 and advisor_id is 1 then set score to 90 and if student_id is 1 and advisor_id is 2 then set score to 80 and so on .... Each of student is evaluated by 3 advisors. In my scores table I have id, student_id, advisor_id, score.

Below is an example JSON data which is passed by user.

 {
      "students": [
          {
            "student_id": 1,
            "committee": [
                { "advisor_id": 1, "score": 90 },
                { "advisor_id": 2, "score": 80 },
                { "advisor_id": 3, "score": 90 },
              ]
          },
          {
            "student_id": 2,
            "committee": [
                { "advisor_id": 1, "score": 70 },
                { "advisor_id": 2, "score": 70 },
                { "advisor_id": 3, "score": 80 },
              ]
          },
          {
            "student_id": 3,
            "committee": [
                { "advisor_id": 1, "score": 90 },
                { "advisor_id": 2, "score": 70 },
                { "advisor_id": 3, "score": 70 },
              ]
          },
          {
            "student_id": 4,
            "committee": [
                { "advisor_id": 1, "score": 80 },
                { "advisor_id": 2, "score": 90 },
                { "advisor_id": 3, "score": 80 },
              ]
          },

        ]  
    }

Kalpeshdav · Accepted Answer · 2018-03-08 09:59:48Z

1

You can do it this way:

var queries = '';

  values.forEach(function (item) {
    queries += mysql.format("UPDATE tabletest SET score = 90 WHERE 
  student_id = 1 AND advisor_id = 1;");
    queries += mysql.format("UPDATE tabletest SET score = 80 WHERE 
  student_id = 1 AND advisor_id = 2;");
  });

  connection.query(queries, defered.makeNodeResolver());

To use multiple statements feature you have to enable it for your connection:

var connection = mysql.createConnection({
  ...
  multipleStatements: true,
});

answered Mar 8, 2018 at 9:59

Kalpeshdav

1812 bronze badges

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

Collectives™ on Stack Overflow

Update multiple records with mysql and nodeJs

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related