SQL DELETE

SQL
POSTGRES
INTERACTIVE

Complete SQL DELETE Technical Guide

1. Fundamental Syntax 
DELETE FROM table_name
[WHERE conditions]
[ORDER BY column]
[LIMIT row_count];
  1. WHERE clause: Critical for targeting specific rows (omitting deletes all rows)
  2. ORDER BY + LIMIT: MySQL/MariaDB extension for controlled deletion
  3. Returning deleted rows: PostgreSQL/SQL Server support RETURNING clause
2. Advanced Deletion Patterns
Join Deletions
-- MySQL/SQL Server syntax
DELETE t1 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.status = 'expired';
Subquery Deletions
DELETE FROM products
WHERE id IN (
  SELECT product_id FROM inventory 
  WHERE stock = 0
);
Batch Deletion
-- PostgreSQL example
WITH batch AS (
  SELECT id FROM large_table
  WHERE condition
  LIMIT 1000
)
DELETE FROM large_table
WHERE id IN (SELECT id FROM batch);
3. Database-Specific Features
PostgreSQL
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING order_id, customer_id;
SQL Server
DELETE TOP (1000) FROM audit_log
WHERE created_at < DATEADD(year, -1, GETDATE());
Oracle
DELETE FROM employees
WHERE department_id = 10
RETURNING employee_id INTO :deleted_ids;
MySQL
DELETE LOW_PRIORITY QUICK IGNORE FROM temp_data
WHERE expires_at < NOW()
ORDER BY created_at
LIMIT 10000;
4. Performance Considerations
  1. Index Usage: WHERE conditions should use indexes for large tables
  2. Table Locking: Mass deletions may lock entire tables
  3. Foreign Key Overhead: ON DELETE CASCADE triggers can be expensive
  4. Vacuum/Reclaim Space: Some DBs require maintenance after large deletes
Batch Deletion Strategy
-- SQL Server pattern
WHILE EXISTS (SELECT 1 FROM logs WHERE created_at < @cutoff)
BEGIN
  DELETE TOP (10000) FROM logs
  WHERE created_at < @cutoff;
  
  WAITFOR DELAY '00:00:01'; -- Pause to reduce contention
END
5. Error Handling & Safety
  1. Backup First: Always backup before mass deletions
  2. Transaction Blocks: Wrap in transactions for atomicity
  3. Test with SELECT: Verify target rows before deleting
  4. Constraint Violations: Foreign key references may block deletions
Safe Deletion Pattern
BEGIN TRANSACTION;

-- First verify
SELECT COUNT(*) FROM customers WHERE last_activity < '2010-01-01';

-- Then delete in batches
DELETE FROM customers 
WHERE last_activity < '2010-01-01'
AND customer_id IN (
  SELECT customer_id FROM customers
  WHERE last_activity < '2010-01-01'
  ORDER BY customer_id
  LIMIT 1000
);

-- Verify before committing
COMMIT;
-- or ROLLBACK if issues found
6. Advanced Scenarios
Partitioned Table Deletions
-- PostgreSQL example
DELETE FROM measurements
WHERE log_time < CURRENT_DATE - INTERVAL '1 year'
AND log_time >= CURRENT_DATE - INTERVAL '2 years';
Temporal Table Handling
-- SQL Server system-versioned tables
DELETE FROM Employees
FOR SYSTEM_TIME AS OF '2023-01-01'
WHERE EmployeeID = 123;
Cascading Deletes
-- Schema definition example
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  ...
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
);

Advanced DELETE Topics

1. Replication Impact 
# MySQL binlog analysis
SHOW BINLOG EVENTS IN 'mysql-bin.000123' 
WHERE table_name = 'deleted_records';
  1. Row-based replication: Logs individual deleted rows (large binary logs)
  2. Statement-based replication: Logs DELETE statements (potential inconsistency if non-deterministic)
  3. Delayed replication: Large deletes may cause significant replication lag
  4. Conflict resolution: Special handling needed in multi-master setups
2. MVCC Internals 
-- PostgreSQL visibility check
SELECT xmin, xmax, * FROM accounts 
WHERE account_id = 456;
  1. Version chains: Deleted rows marked as invisible but remain until vacuumed
  2. Transaction ID tracking: xmax field indicates deleting transaction
  3. Snapshot isolation: Other transactions may still see "deleted" rows
  4. Write Skew: Phantom reads during concurrent deletes
3. Materialized View Refresh 
-- Oracle materialized view log
BEGIN
  DBMS_MVIEW.REFRESH(
    list => 'CUSTOMER_SUMMARY',
    method => 'FAST'
  );
END;
  1. Fast refresh: Uses change logs for incremental updates
  2. Complete refresh: Rebuilds entire view after mass deletes
  3. Transaction consistency: Some systems defer refresh until commit
  4. Performance impact: Multiple views can slow down deletions
4. Distributed Deletes 
// Cassandra tombstone example
DELETE FROM user_sessions 
WHERE user_id = 789 
AND session_id = 'abc123'
USING TIMESTAMP 1630000000000;
  1. Tombstones: Special markers for deleted data in NoSQL systems
  2. Consistency levels: QUORUM vs ALL vs ONE tradeoffs
  3. Hinted handoff: Handling deletes during node outages
  4. Read repair: Background consistency maintenance
5. Storage Reclamation 
# PostgreSQL vacuum analysis
VACUUM (VERBOSE, ANALYZE) large_table;
  1. Vacuum processes: PostgreSQL's dead tuple cleanup
  2. Autovacuum tuning: Adjusting thresholds for optimal performance
  3. Page compaction: InnoDB's space reclamation mechanisms
  4. Shrink operations: SQL Server's DBCC SHRINKFILE
6. Trigger Execution 
-- SQL Server INSTEAD OF DELETE trigger
CREATE TRIGGER archive_deleted_orders
ON orders INSTEAD OF DELETE
AS
BEGIN
  INSERT INTO order_archive
  SELECT *, GETDATE() FROM deleted;
  
  DELETE o FROM orders o
  JOIN deleted d ON o.order_id = d.order_id;
END;
  1. Trigger timing: BEFORE vs AFTER vs INSTEAD OF
  2. Deleted pseudo-tables: Special temporary tables in triggers
  3. Cascading effects: Trigger chains and recursion limits
  4. Performance overhead: Measuring trigger impact on delete operations