Node.js MySQL – UPDATE Table Query – Examples

Node.js MySQL UPDATE Table Query

We can update records of a table using MySQL UPDATE Query.

In this tutorial, we will learn how to update records of a table based on WHERE clause, and also learn how to update all records of a table.

• MySQL Update record(s) of a table
• MySQL Update all records of a table

Example 1 – Update Row(s) of MySQL Table via Node.js

Consider the case that due to manual error, records in the students table are inserted with marks as 74 instead of 84. Now we shall execute a MySQL Update Query that updates marks column with value 84 where there is a value of 74.

UpdateRecordsFiltered.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("UPDATE students SET marks=84 WHERE marks=74", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Open a terminal or command prompt and run this script using node command as shown in the following.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node UpdateRecordsFiltered.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 3  Changed: 3  Warnings: 0',
  protocol41: true,
  changedRows: 3 }

Example 2 – Update All Records of MySQL Table via Node.js

Consider a scenario where the exam conducted to the students is cancelled due to some reason, you want to reconduct the exam. So, you would like to update the marks for all the students to 0. Following Node.js MySQL UPDATE Query Example shows how to update all the records of a table.

UpdateAllRecords.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("UPDATE students SET marks=84", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Observe that there is no WHERE clause in the Query because of which all the records are selected for update process.

Conclusion

In this Node.js Tutorial – Node.js MySQL – UPDATE Table Query, we have learnt to UPDATE records based on a condition or UPDATE all the records of a table.