Node.js - MySQL Update

---

---

A data-driven Node.js application often needs to modify one or more records stored in a MySQL database. This is done by passing the UPDATE query string as an argument to the mysql.query() method. Quite often, the data with an existing record is to be updated, comes in the form of user input, such as a HTML form posted in a Node.js web based application. In this chapter, you'll learn how to execute MySQL UPDATE query. Starting with a simple UPDATE, using a prepared statement and UPDATE with JOIN will be demonstrated with the help of suitable Node.js examples.

Simple UPDATE

The syntax of a basic UPDATE statement in MySQL is as follows −

UPDATE table_name 
SET 
   column_name1 = expr1, column_name2 = expr2,  ...
WHERE condition;

Assuming that a database mydb is available on the MySQL server, and the employee table is present with the following data −

mysql> select * from employee;
+----+-------+------+--------+
| id | name  | age  | salary |
+----+-------+------+--------+
|  1 | Ravi  |   25 |  25000 |
|  2 | Anil  |   26 |  30000 |
|  3 | Meena |   26 |  27000 |
+----+-------+------+--------+

Example

The following program updates the salary field of employee table, increasing the salary of each employee by Rs.500

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry ="UPDATE employee SET salary=salary+500;";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function(err) {
      if (err) throw err;
      console.log("Records updated successfully");
   });
});

After running the above code, go to the MySQL command-line client and check the rows in employee table −

mysql> select * from employee;
+----+-------+------+--------+
| id | name  | age  | salary |
+----+-------+------+--------+
|  1 | Ravi  |   25 |  25500 |
|  2 | Anil  |   26 |  30500 |
|  3 | Meena |   26 |  27500 |
+----+-------+------+--------+

You can also add a loop as follows in the code to see the employee records

qry =`SELECT name,salary FROM employee;`;
con.query(qry, function (err, results) {
   if (err) throw err;
   console.log(results);
});

Output

[
  RowDataPacket { name: 'Ravi', salary: 25500 },
  RowDataPacket { name: 'Anil', salary: 30500 },
  RowDataPacket { name: 'Meena', salary: 27500 }
]

UPDATE with Prepared statement

MySQL supports prepared statements. You can build the query dynamically by inserting variable data in the placeholders embedded in the query string. MySQL uses ? symbol as the placeholder.

var qry ="UPDATE employee SET salary=40000 WHERE name=?;";
var nm = "Anil";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, nm, function(err) {
      if (err) throw err;
      console.log("Records updated successfully");
      var qry =`SELECT name,salary FROM employee WHERE name=?;`;
      con.query(qry,nm, function (err, results) {
         if (err) throw err;
         console.log(results);
      });
   });
});

This will update the salary of employee with Anil as name to Rs. 40000

[ RowDataPacket { name: 'Anil', salary: 40000 } ]

UPDATE JOIN

The JOIN clause is more commonly used in SELECT query to retrieve data from two or more related tables. You can also include JOIN clause in UPDATE query also, to perform cross-table updates.

For this example, we shall create two tables with a common field to establish PRIMARY KEY − FOREIGN KEY relationship between the two.

merits table

CREATE TABLE merits (
   performance INT(11) NOT NULL,
   percentage FLOAT NOT NULL,
   PRIMARY KEY (performance)
);

Add some data −

INSERT INTO merits(performance,percentage)
VALUES(1,0),
      (2,0.01),
      (3,0.03),
      (4,0.05),
      (5,0.08);
      (4,0.05),
      (5,0.08);

The contents of merits table −

mysql> select * from merits;
+-------------+------------+
| performance | percentage |
+-------------+------------+
|           1 |          0 |
|           2 |       0.01 |
|           3 |       0.03 |
|           4 |       0.05 |
|           5 |       0.08 |
+-------------+------------+

employees table

CREATE TABLE employees (
   emp_id INT(11) NOT NULL AUTO_INCREMENT,
   emp_name VARCHAR(255) NOT NULL,
   performance INT(11) DEFAULT NULL,
   salary FLOAT DEFAULT NULL,
   PRIMARY KEY (emp_id),
   CONSTRAINT fk_performance FOREIGN KEY (performance)
      REFERENCES merits (performance)
);

In this table, performance is the foreign key, referring to the key of same name in merits table.

Add some data −

INSERT INTO employees(emp_name,performance,salary)      
VALUES('Mary Doe', 1, 50000),
      ('Cindy Smith', 3, 65000),
      ('Sue Greenspan', 4, 75000),
      ('Grace Dell', 5, 125000),
      ('Nancy Johnson', 3, 85000),
      ('John Doe', 2, 45000),
      ('Lily Bush', 3, 55000);

The contents of employees table −

mysql> select * from employees;
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Smith   |           3 |  65000 |
|      3 | Sue Greenspan |           4 |  75000 |
|      4 | Grace Dell    |           5 | 125000 |
|      5 | Nancy Johnson |           3 |  85000 |
|      6 | John Doe      |           2 |  45000 |
|      7 | Lily Bush     |           3 |  55000 |
+--------+---------------+-------------+--------+
7 rows in set (0.00 sec)

We would like to increment the salary of employee based on the percentage related to his performance rating.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`
   UPDATE employees
      INNER JOIN
   merits ON employees.performance = merits.performance 
SET 
   salary = salary + salary * percentage;
   `;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");

   con.query(qry, nm, function(err) {
      if (err) throw err;
      con.query(qry,nm, function (err, results) {
         if (err) throw err;
         console.log(results);
      });
   });
});

Output

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

Check the updated salary field in employees table −

mysql> select * from employees;
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Smith   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
+--------+---------------+-------------+--------+
7 rows in set (0.00 sec)