MySQL DELETE

-- Delete a specific row by primary key
DELETE FROM customers WHERE customer_id = 5;

-- Delete rows matching a condition
DELETE FROM orders WHERE status = 'cancelled';

-- Delete with multiple conditions
DELETE FROM products
WHERE stock = 0 AND category = 'Electronics';

-- Delete with LIMIT (remove at most N rows at a time — useful for large tables)
DELETE FROM orders
WHERE status = 'cancelled'
LIMIT 1000;

-- Preview rows before deleting (always a good practice)
SELECT * FROM orders WHERE status = 'cancelled' LIMIT 10;

-- Delete all rows (use TRUNCATE instead for better performance)
DELETE FROM order_items;

-- DELETE with JOIN: delete order_items for cancelled orders
DELETE oi
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';

-- DELETE from multiple tables in one statement
DELETE o, oi
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'cancelled' AND o.ordered_at < '2023-01-01';

-- DELETE with subquery
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

-- TRUNCATE: fast, resets AUTO_INCREMENT, cannot be rolled back
TRUNCATE TABLE order_items;

-- DELETE: slower, logged, can be rolled back inside a transaction
DELETE FROM order_items;

-- Soft Delete Pattern: instead of deleting, mark rows as deleted
-- Add a deleted_at column to the table
ALTER TABLE customers
    ADD COLUMN deleted_at DATETIME DEFAULT NULL;

-- "Delete" a customer (soft delete)
UPDATE customers
SET deleted_at = NOW()
WHERE customer_id = 5;

-- Query only non-deleted customers
SELECT * FROM customers WHERE deleted_at IS NULL;

-- Restore a soft-deleted customer
UPDATE customers
SET deleted_at = NULL
WHERE customer_id = 5;

-- Permanently purge soft-deleted records older than 90 days
DELETE FROM customers
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL 90 DAY;