• Node.js Video Tutorials

Node.js - MySQL Delete

Out of the CRUD operations to be performed with a MySQL database, the DELETE query helps in removing one or more rows from a table. In this chapter, we shall show how to call MySQL DELETE statement in a Node.js application.

The basic DELETE query has the following syntax −

DELETE FROM table_name
WHERE condition;

Although the WHERE clause is optional, most probably it is invariable used, otherwise it delete all rows in the table.

Simple DELETE

In the following Node.js code, a DELETE query string is passed to the mysql.query() method. The program will delete all the employee records with age>25.

Example

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

var qry ="DELETE FROM employee WHERE age>25;";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");

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

Output

OkPacket {
fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

Drop the WHERE clause from the query string in above code.

var qry ="DELETE FROM employee;";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");

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

Now open the command-line client and run the SELECT query

mysql> select * from employee;
Empty set (0.00 sec)

You can see that there are no records left in the table. The DELETE query without WHERE clause is equivalent to TRUNCATE statement.

var qry ="TRUNCATE employee;";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });
   con.end();
});

LIMIT clause

The LIMIT clause in the DELETE query restricts the delete operation to a specified number. For example, LIMIT 5 deletes only first 5 records in the given order. For this example, we shall use the pre-installed world database, and the city table in it.

Example

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

var qry ="DELETE FROM city WHERE CountryCode='IND' ORDER BY population LIMIT 5;";
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");

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

Output

OkPacket {
  fieldCount: 0,
  affectedRows: 5,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

Originally the City table had 341 rows with IND as CountryCode. With 5 cities in ascending order of population deleted by running the above code, there will be 336 cities left in the table. You can check it by running the SELECT COUNT query before and after running the above code.

mysql> SELECT COUNT(name) from city WHERE CountryCode='IND';
Advertisements