Updating a record in MySQL using NodeJS

In this article, we will see how we can update a record in MySQL using NodeJS. We will dynamically update MySQL table values from Node.js server. You can use the select statement after updating to check if the MySql record is updated.

Before proceeding, please check the following steps are already executed −

• mkdir mysql-test

• cd mysql-test

• npm init -y

• npm install mysql

The above steps are for installing the Node - mysql dependecy in the project folder.

Upadting a Record into the Students Table −

• For updating an existing record into the MySQL table, firstly create an app.js file

• Now copy-paste the below snippet in the file

• Run the code using the following command

>> node app.js

Example

// Checking the MySQL dependency in NPM
var mysql = require('mysql');

// Creating a mysql connection
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;
   var sql = "UPDATE student SET address = 'Bangalore' WHERE name = 'John';"
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result.affectedRows + " Record(s) updated.");
      console.log(result);
   });
});

Output

1 Record(s) updated.
OkPacket {
   fieldCount: 0,
   affectedRows: 1, // This will return the number of rows updated.
   insertId: 0,
   serverStatus: 34,
   warningCount: 0,
   message: '(Rows matched: 1 Changed: 1 Warnings: 0', // This will return the
   number of rows matched.
   protocol41: true,
   changedRows: 1 }

Example

// Checking the MySQL dependency in NPM
var mysql = require('mysql');

// Creating a mysql connection
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;
   // Updating the fields with address while checking the address
   var sql = "UPDATE student SET address = 'Bangalore' WHERE address = 'Delhi';"
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result.affectedRows + " Record(s) updated.");
      console.log(result);
   });
});

Output

3 Record(s) updated.
OkPacket {
   fieldCount: 0,
   affectedRows: 3, // This will return the number of rows updated.
   insertId: 0,
   serverStatus: 34,
   warningCount: 0,
   message: '(Rows matched: 3 Changed: 3 Warnings: 0', // This will return the number of rows matched.
   protocol41: true,
   changedRows: 3 }