SQL DELETE Statement ❌¶

Prerequisites: SELECT Statement

Mentor's Note: Deleting data is permanent (unless you have a backup!). Think of it as throwing something into an incinerator. Once the command finishes, that row is gone forever. 💨

🌟 The Scenario: The Customer Cancellation 🏃‍♂️¶

Imagine a customer wants to close their account. - Hard Delete: You shred their paper file and throw it away. (Traditional DELETE). - Soft Delete: You put a stamp on their file saying "INACTIVE" but keep the file in the cabinet. (Modern Industry Standard).

💻 1. The Basic Syntax¶

DELETE FROM table_name
WHERE condition;

Example: Delete a specific record¶

-- Scenario: Remove student who dropped out
DELETE FROM students
WHERE roll_no = 108;

💻 2. Deleting All Records 🌪️¶

If you omit the WHERE clause, the table will be emptied, but the table structure (columns) remains.

-- ⚠️ High Risk Command
DELETE FROM log_table;

Note: For large tables, use TRUNCATE instead of DELETE ALL for much faster performance. (See DROP vs TRUNCATE).

📖 3. Advanced Concept: Soft Delete 🛡️¶

Professional developers rarely use the DELETE command for important data like Customers or Orders. Instead, they use a "Soft Delete".

Step 1: Add a column like is_deleted or status.

ALTER TABLE customers ADD status VARCHAR(10) DEFAULT 'Active';

Step 2: Instead of deleting, just UPDATE.

-- "Soft Deleting" a user
UPDATE customers 
SET status = 'Deleted' 
WHERE id = 501;

Result: You can still see their history for reporting, but they won't appear in the active app!

📊 Summary: DELETE vs UPDATE (Soft)¶

Feature	Hard DELETE	Soft UPDATE
Recovery	Impossible (without backup)	Easy (just change status)
Audit Trail	Lost 📉	Preserved 📈
DB Size	Shrinks	Grows

💡 Pro Tip¶

"Always use a Primary Key in your DELETE WHERE clause. Deleting by name is dangerous because two people might have the same name!"