#DELETE Data

DELETE FROM table_name
WHERE condition;

-- Delete user by ID
DELETE FROM users
WHERE id = 1;

-- Delete all inactive users
DELETE FROM users
WHERE status = 'inactive';

-- Delete users with no orders
DELETE FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

-- MySQL syntax
DELETE u
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Delete all data (keeps table structure)
DELETE FROM users;

-- Faster alternative: TRUNCATE
TRUNCATE TABLE users;

-- Delete orders older than 1 year
DELETE FROM orders
WHERE order_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete in batches to avoid locking
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;

START TRANSACTION;

-- Delete user
DELETE FROM users WHERE id = 1;

-- Delete related data
DELETE FROM user_profiles WHERE user_id = 1;
DELETE FROM orders WHERE user_id = 1;

COMMIT;

-- Foreign key with CASCADE
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

-- Deleting user automatically deletes their orders
DELETE FROM users WHERE id = 1;

-- Instead of deleting, mark as deleted
UPDATE users
SET deleted_at = NOW(), status = 'deleted'
WHERE id = 1;

-- Query only active users
SELECT * FROM users WHERE deleted_at IS NULL;

-- DANGER: Deletes all rows!
DELETE FROM users;

-- Correct: Use WHERE clause
DELETE FROM users WHERE id = 1;

-- Error: Cannot delete due to foreign key
DELETE FROM users WHERE id = 1;
-- Error: Cannot delete or update a parent row

-- Solution 1: Delete child records first
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- Solution 2: Use CASCADE

-- Create index for WHERE columns
CREATE INDEX idx_status ON users(status);

-- Batch delete
DELETE FROM logs 
WHERE created_at < '2023-01-01' 
LIMIT 10000;