MySQL Delete

The DELETE statement in MySQL is used to remove one or more rows from a table. When integrated with Node.js, it enables dynamic and programmatic deletion of data in your applications.

Why Use DELETE in MySQL?

  1. Remove Unwanted Data: Clean up old or irrelevant records from your database.
  2. Dynamic Deletion: Delete specific rows based on user actions or application logic.
  3. Efficient Data Management: Keep your database optimized by removing unnecessary data.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running.

Node.js Installed: Install Node.js if you haven’t already.

MySQL Package Installed: Install the MySQL module using:

npm install mysql

Setting Up the Node.js Project

Initialize the Project:

npm init -y

Install the MySQL Package:

npm install mysql

Create a File: Create a file named app.js.

Connecting to the MySQL Database

To delete records, you need to establish a connection to your MySQL database.

Example: Connect to MySQL

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'nodejs_demo', // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err);
return;
}
console.log('Connected to MySQL successfully!');
});

Using the DELETE Statement in Node.js

The DELETE statement allows you to specify conditions for removing specific rows. Let’s explore different scenarios with examples.

Example 1: Delete a Single Record

Delete a row based on a specific condition.

const sql = 'DELETE FROM users WHERE id = ?';
const id = 1;

connection.query(sql, [id], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

  • WHERE id = ?: Ensures only the row with the specified id is deleted.
  • affectedRows: Indicates how many rows were deleted.

Example 2: Delete Multiple Records

You can delete multiple rows by specifying a condition that matches more than one record.

const sql = 'DELETE FROM users WHERE age > ?';
const age = 30;

connection.query(sql, [age], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

  • WHERE age > ?: Deletes all users older than 30.

Deleting All Records (Use with Caution)

To delete all rows from a table, omit the WHERE clause. Be extremely careful with this operation.

Example 3: Delete All Records

const sql = 'DELETE FROM users';

connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting all data:', err);
return;
}
console.log('All rows deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Warning:

  • Without the WHERE clause, all rows in the table will be deleted.

Using DELETE with LIMIT

You can limit the number of rows to delete using the LIMIT clause.

Example 4: Delete a Limited Number of Rows

const sql = 'DELETE FROM users ORDER BY age ASC LIMIT 1';

connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

  • ORDER BY age ASC: Sorts rows by age in ascending order.
  • LIMIT 1: Deletes only the first row from the sorted result.

Combining DELETE with Other Clauses

You can combine DELETE with clauses like JOIN or WHERE to target specific rows.

Example 5: Delete Using a Subquery

const sql = `
DELETE FROM users
WHERE id IN (
SELECT id FROM users WHERE age < ? LIMIT 2
)
`;
const age = 20;

connection.query(sql, [age], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted using subquery:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

  • SELECT id FROM users WHERE age < ?: Fetches rows where age is less than 20.
  • LIMIT 2: Deletes only two rows matching the condition.

Handling Errors

Always handle errors to ensure your application remains stable.

Example: Error Handling

const sql = 'DELETE FROM non_existing_table WHERE id = ?';
const id = 1;

connection.query(sql, [id], (err, result) => {
if (err) {
console.error('Error deleting data:', err.message);
return;
}
console.log('Rows deleted:', result.affectedRows);
});

Output:

Error deleting data: Table 'nodejs_demo.non_existing_table' doesn't exist

Real-World Use Case

Imagine building a user management system where admins can delete inactive users.

Example: Delete Inactive Users

const sql = 'DELETE FROM users WHERE last_login < NOW() - INTERVAL 1 YEAR';

connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting inactive users:', err);
return;
}
console.log('Inactive users deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

  • last_login < NOW() – INTERVAL 1 YEAR: Deletes users who haven’t logged in for over a year.

Leave a Comment

BoxofLearn