DELETE Data Patterns

-- Delete single row
DELETE FROM users 
WHERE id = 1;

-- Delete with multiple conditions
DELETE FROM sessions 
WHERE expired_at < NOW() 
  AND user_id IS NOT NULL;

-- Delete old records
DELETE FROM logs 
WHERE created_at < NOW() - INTERVAL '90 days';

-- Delete with subquery
DELETE FROM users 
WHERE id IN (
  SELECT user_id 
  FROM banned_users
);

-- Delete with JOIN (MySQL)
DELETE u 
FROM users u
INNER JOIN deleted_accounts d ON u.email = d.email;

-- Delete and return (PostgreSQL)
DELETE FROM users 
WHERE id = 1
RETURNING *;

-- Delete all rows (BE CAREFUL!)
DELETE FROM temp_table;

-- Better: TRUNCATE (faster, resets auto-increment)
TRUNCATE TABLE temp_table;

-- Soft delete (recommended over hard delete)
UPDATE users 
SET deleted_at = NOW(), status = 'deleted'
WHERE id = 1;

-- Delete with LIMIT (MySQL - for batch deletes)
DELETE FROM logs 
WHERE created_at < '2023-01-01'
LIMIT 1000;

-- Batch delete loop (MySQL)
WHILE (SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01') > 0
DO
  DELETE FROM logs 
  WHERE created_at < '2023-01-01'
  LIMIT 1000;
  -- Pause between batches
  SELECT SLEEP(1);
END WHILE;