Node.js MySQL Update data Database Table

456 views 6 months ago NodeJS

In this tutorial article, we will learn how to update data from MySQL database using Node.js. We will use where condition to update specific records into MySQL server.

Perform the following steps to update data into MySQL database server.

Step 1: First create connection between MySQL server and Node.js.

Step 2: After database connection created, use conn.query() method to run UPDATE query into MySQL database.

var query = `UPDATE visitors SET percentage = ? WHERE id = ?`;
var percentage = [62.02, 1];

Step 3: After the query executed, close the connection using conn.end() method.

We have created below update_rows.js file with full example.

const mysql = require('mysql');
const conn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    database: 'charts',
    user: 'root',
    password: 'root',
});
conn.connect((err) => {
    if (err) {
        return console.log(err.message);
    } else {
        // select query
        var query = `UPDATE visitors SET percentage = ? WHERE id = ?`;
        var percentage = [62.02, 1];
        // query to database
        conn.query(query, percentage, function(err, response, result) {
            if (err) {
                return console.log(err.message);
            } else {
                console.log(response);
            }
        });
    }
    // close the connection
    conn.end();
});

Now run the file into Node server.

node update_rows.js

The application will response with object data.

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1
}

You can get updated records number using response.affectedRows object property.

Note: If you omit the WHERE condition, all records will be updated.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]