DELETE with WHERE condition in SQL

Introduction

We use DELETE with WHERE to remove only specific rows from a table, not everything. This helps keep the data we want and remove the rest.

When you want to remove a customer's record who closed their account.

When deleting outdated or incorrect entries from a list.

When cleaning up test data after trying something new.

When removing all orders before a certain date.

When deleting users who have not logged in for a long time.

Syntax

SQL

DELETE FROM table_name WHERE condition;

The WHERE clause decides which rows to delete.

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

Examples

Deletes the employee whose ID is 5.

SQL

DELETE FROM employees WHERE employee_id = 5;

Deletes all orders placed before January 1, 2023.

SQL

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

Deletes all users marked as inactive.

SQL

DELETE FROM users WHERE status = 'inactive';

Sample Program

This creates a products table, adds three items, deletes those with price less than 10, then shows remaining items.

SQL

CREATE TABLE products (id INT, name VARCHAR(20), price INT);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 50), (3, 'Pencil', 5);
DELETE FROM products WHERE price < 10;
SELECT * FROM products;

Important Notes

Always double-check your WHERE condition before running DELETE to avoid removing too much data.

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

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

Summary

DELETE with WHERE removes only rows matching the condition.

Without WHERE, DELETE removes all rows in the table.

Test your condition with SELECT before deleting.