DELETE with WHERE clause in MySQL

Introduction

You use DELETE with WHERE to remove only specific rows from a table, not everything.

When you want to delete a user who canceled their account.

When you need to remove outdated records from a sales table.

When cleaning up test data that matches certain conditions.

When deleting entries older than a certain date.

When removing duplicate records based on a condition.

Syntax

MySQL

DELETE FROM table_name WHERE condition;

The WHERE clause tells which rows to delete.

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

Examples

Deletes the employee with ID 5.

MySQL

DELETE FROM employees WHERE employee_id = 5;

Deletes orders placed before January 1, 2023.

MySQL

DELETE FROM orders WHERE order_date < '2023-01-01';

Deletes all products in the 'obsolete' category.

MySQL

DELETE FROM products WHERE category = 'obsolete';

Sample Program

This creates a customers table, adds three customers, deletes the one named 'Bob', then shows the remaining customers.

MySQL

CREATE TABLE customers (id INT, name VARCHAR(20));
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
DELETE FROM customers WHERE name = 'Bob';
SELECT * FROM customers ORDER BY id;

Important Notes

Always double-check your WHERE clause before running DELETE to avoid removing wrong data.

You can use multiple conditions with AND or OR in the WHERE clause.

Use SELECT with the same WHERE clause first to see which rows will be deleted.

Summary

DELETE removes rows from a table.

WHERE clause limits which rows get deleted.

Without WHERE, DELETE removes all rows.