|
Getting your Trinity Audio player ready...
|
When working as a Data Analyst or MIS professional, you will often need to:
- Remove wrong entries
- Delete duplicate data
- Clear test records
- Remove old sales or inactive customers
That’s where the DELETE statement comes in.
But ⚠️ be careful — DELETE can permanently remove data!
What is DELETE in SQL?
The DELETE statement is used to remove existing rows from a table.
Basic Syntax (Same in PostgreSQL & MySQL)
DELETE FROM table_name
WHERE condition;👉 The WHERE clause is very important.
If you don’t use it, all rows will be deleted.
FMCG Dataset Example
Assume we have these tables:
- employees
- customers
- products
- sales
- inventory
Let’s see practical scenarios.
Delete Specific Record
Example: Delete a customer with ID = 105
DELETE FROM customers
WHERE customer_id = 105;✔ Only that customer will be removed.
Read More: INSERT Data into Tables in SQL
Delete Multiple Rows
Example: Remove all inactive customers
DELETE FROM customers
WHERE status = 'Inactive';Very common in real-world data cleaning.
Delete Based on Date Condition
Example: Remove old sales before 2022
PostgreSQL
DELETE FROM sales
WHERE sale_date < '2022-01-01';MySQL
DELETE FROM sales
WHERE sale_date < '2022-01-01';✅ Same syntax in both databases.
Delete Using Subquery
Suppose you want to delete sales where the product no longer exists.
PostgreSQL & MySQL
DELETE FROM sales
WHERE product_id NOT IN (
SELECT product_id FROM products
);This helps maintain clean and consistent data.
Delete with JOIN (Important Difference)
Sometimes you need to delete records based on another table.
PostgreSQL
DELETE FROM sales
USING products
WHERE sales.product_id = products.product_id
AND products.is_discontinued = TRUE;MySQL
DELETE sales
FROM sales
JOIN products
ON sales.product_id = products.product_id
WHERE products.is_discontinued = 1;⚡ Syntax differs slightly — very important for interviews!
Delete All Data (But Keep Table Structure)
If you want to remove all rows:
DELETE FROM inventory;⚠ This removes all records but keeps table structure.
DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes specific rows? | ✅ Yes | ❌ No |
| Uses WHERE? | ✅ Yes | ❌ No |
| Faster? | Slower | Faster |
| Can rollback (inside transaction)? | ✅ Yes | Depends |
PostgreSQL
TRUNCATE TABLE inventory;MySQL
TRUNCATE TABLE inventory;Use TRUNCATE only when you want to remove all data quickly.
What Happens If You Forget WHERE?
DELETE FROM customers;💥 All rows will be deleted!
Always test first:
SELECT * FROM customers
WHERE status = 'Inactive';Then convert to DELETE.
Best Practices for Data Analysts
✔ Always run SELECT first
✔ Use transactions when possible
✔ Take backup before mass deletion
✔ Avoid deleting production data directly
✔ Prefer soft delete (add status column) in real systems
Example of Soft Delete:
UPDATE customers
SET status = 'Deleted'
WHERE customer_id = 105;Much safer in business systems.
Read More: UPDATE Data in SQL
Real-World Data Cleaning Scenario
Suppose sales table contains test records:
DELETE FROM sales
WHERE customer_name = 'Test User';Or remove NULL entries:
DELETE FROM sales
WHERE sale_amount IS NULL;This is very common in MIS and reporting systems.
Summary
The DELETE statement is used to remove data from tables in PostgreSQL and MySQL. It supports conditional deletion using WHERE and can work with subqueries and joins. Always use DELETE carefully because data removal is permanent. In real-world data analysis, DELETE is frequently used to clean incorrect, duplicate, or outdated records. Understanding DELETE and TRUNCATE differences is essential for interviews and practical SQL work.