SQL Delete

The DELETE statement is used to remove rows from a table based on specified conditions. This statement is a core data manipulation tool, enabling you to clean up or archive data, and can incorporate subqueries or joins to target rows based on criteria from other tables.

---

Basic Syntax

DELETE FROM table_name
WHERE condition;

• table_name: The table from which rows will be removed.
• WHERE: Filters which rows are deleted. Omitting this clause deletes all rows from the table.

---

Example 1: Deleting Data from a Single Table

Delete an employee record with a specific employee_id:

DELETE FROM employees
WHERE employee_id = 123;

Explanation:

• Only the row with employee_id = 123 is removed from the employees table.

---

Example 2: Deleting Data Using a Subquery

Delete orders associated with customers marked as ‘inactive’. This uses a subquery to select the relevant customer IDs:

DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'inactive'
);

Explanation:

• The subquery retrieves customer_ids from the customers table where the customer is inactive.
• The DELETE statement then removes all orders from the orders table that match these IDs.

---

Example 3: Deleting Data with a Join (PostgreSQL)

PostgreSQL supports using joins in DELETE statements, which can be more efficient or clearer for certain operations. For instance, to delete orders for inactive customers:

DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.customer_id
  AND customers.status = 'inactive';

Explanation:

• The USING clause allows you to join orders with customers.
• The WHERE clause targets rows where the customer’s status is ‘inactive’.
• This approach directly relates rows in orders with rows in customers and deletes the matching ones.

---

Related Notes

• SQL Insert – How to add new rows to a table.
• SQL Update – How to modify existing rows in a table.
• SQL Transactions and Errors – Grouping SQL statements to maintain atomicity.
• SQL select distinct – Retrieving data from tables.

---

Final Notes

• Always include a WHERE clause unless your intention is to clear the entire table.
• Test deletion queries in a transaction or on a backup copy to prevent unintended data loss.
• Subqueries and joins are powerful for targeting specific data, especially when deletion criteria involve multiple tables.