DELETE

Overview

The DELETE statement is used to delete existing records from a table. It permanently removes rows that match specified conditions.

CRITICAL WARNING: DELETE operations cannot be easily undone. Always use WHERE with DELETE to avoid deleting all rows in the table.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Delete Specific Rows

Always include a WHERE clause to specify which rows to delete:

DELETE FROM Customers
WHERE customer_id = 101;

Delete Multiple Rows

Delete all rows that match the WHERE condition:

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

DANGER: Delete All Rows

WARNING: Without WHERE clause, DELETE removes ALL rows from the table:

-- ⚠️ This deletes ALL customers!
DELETE FROM Customers;

-- ✅ Safe: Only deletes inactive customers
DELETE FROM Customers
WHERE status = 'Inactive';

Examples

Example 1: Delete Single Record

DELETE FROM Customers
WHERE customer_id = 101;

Example 2: Delete Based on Condition

DELETE FROM Orders
WHERE order_date < '2020-01-01'
  AND status = 'Completed';

Example 3: Delete with Multiple Conditions

DELETE FROM Products
WHERE category = 'Discontinued'
  AND stock_quantity = 0
  AND last_sold_date < '2022-01-01';

Example 4: Delete with IN

DELETE FROM Customers
WHERE customer_id IN (101, 102, 103, 104);

Example 5: Delete Using JOIN

-- SQL Server, PostgreSQL
DELETE o
FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.status = 'Closed';

-- MySQL
DELETE o FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.status = 'Closed';

Delete vs TRUNCATE vs DROP

Understand the difference between DELETE, TRUNCATE, and DROP:

Referential Integrity

DELETE operations must respect foreign key constraints:

-- This will fail if Orders reference this Customer
DELETE FROM Customers
WHERE customer_id = 101;

-- Solution: Delete related orders first, or use CASCADE
DELETE FROM Orders WHERE customer_id = 101;
DELETE FROM Customers WHERE customer_id = 101;

Soft Delete vs Hard Delete

Consider using "soft delete" instead of physically removing records:

-- Instead of DELETE, mark as deleted
UPDATE Customers
SET deleted = 1, deleted_date = GETDATE()
WHERE customer_id = 101;

-- Then filter out deleted records in queries
SELECT * FROM Customers WHERE deleted = 0;

Best Practices

• Always use WHERE: Never DELETE without WHERE clause
• Test with SELECT first: Use SELECT with same WHERE to preview
• Use transactions: Wrap DELETE in transaction for rollback
• Backup first: Backup data before deletion
• Check dependencies: Ensure no foreign key violations
• Consider soft delete: Mark records as deleted instead
• Log deletions: Keep audit trail of deleted records

Safety Checklist

-- Step 1: Preview what will be deleted
SELECT * FROM Customers
WHERE customer_id = 101;

-- Step 2: Check how many rows will be deleted
SELECT COUNT(*) FROM Customers
WHERE customer_id = 101;

-- Step 3: Check for dependencies
SELECT COUNT(*) FROM Orders
WHERE customer_id = 101;

-- Step 4: Perform delete (in transaction)
BEGIN TRANSACTION;

DELETE FROM Customers
WHERE customer_id = 101;

-- Step 5: Verify the deletion
SELECT * FROM Customers
WHERE customer_id = 101;

-- Step 6: Commit or rollback
COMMIT;
-- or ROLLBACK; if something is wrong

Common Use Cases

• Removing old or obsolete records
• Deleting duplicate entries
• Cleaning up test data
• Removing invalid or corrupted records
• Archiving old data before deletion

Common Mistakes

• Forgetting WHERE clause: Deletes all rows!
• Wrong WHERE condition: Deletes unintended rows
• Not checking dependencies: Foreign key constraint errors
• No backup: Can't recover if something goes wrong
• Not using transactions: Can't rollback mistakes

Performance Considerations

• DELETE is slow: Logs each deletion, slower than TRUNCATE
• Use indexes: WHERE conditions benefit from indexes
• Delete in batches: For large deletions, delete in chunks
• Consider TRUNCATE: For deleting all rows, TRUNCATE is faster

Next Steps

Learn about aggregate functions like MIN and MAX, or explore data retrieval with COUNT.