CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Leveni Leon
Leveni Leon

Reputation: 1

Update multiple records with mysql and nodeJs

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 },
              ]
          },

        ]  
    }

Upvotes: 0

Views: 894

Answers (1)

Kalpeshdav
Kalpeshdav

Reputation: 181

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

Upvotes: 1

Related Questions