SQLite: Deleting Rows based on Data from Another Table

There are two common scenarios for deleting with JOINs in SQLite:

Important points to remember:

• Double-check your WHERE clause to ensure you're deleting the intended data.
• Always have a backup before making significant changes.
• Be cautious with DELETE statements, as they permanently remove data.

Tables:

• OrderItems (item_id, order_id, product_name, quantity)
• Orders (order_id, customer_name, order_date)

Objective: Delete Order Items where the corresponding Order has already been fulfilled (order_id not found in Orders table).

DELETE FROM OrderItems
  WHERE order_id NOT IN (SELECT order_id FROM Orders);

Explanation:

• Rows in OrderItems where order_id doesn't exist in Orders (due to fulfilled orders) are deleted.
• This query uses a LEFT JOIN (implicit in NOT IN) to compare order_id in both tables.

Scenario 2: Deleting Customer and all Orders

• Customers (customer_id, name, email)

Objective: Delete a specific customer (by ID) and all their associated orders.

DELETE FROM Orders
  USING Customers
 WHERE Orders.customer_id = Customers.customer_id
   AND Customers.customer_id = 10;  -- Replace 10 with the actual ID

DELETE FROM Customers
 WHERE customer_id = 10;

• The USING clause specifies the table used in the join for filtering.
• This uses a two-step approach:
  1. DELETE from Orders referencing Customers table to delete orders for the specific customer ID (10 in this case).
  2. DELETE from Customers to remove the customer itself.

You can use a subquery within the WHERE clause of your DELETE statement. The subquery retrieves the IDs of rows to be deleted from the other table.

Here's an example for scenario 1 (deleting OrderItems):

DELETE FROM OrderItems
WHERE order_id IN (
  SELECT order_id FROM Orders
  WHERE order_status = 'Fulfilled'  -- Adjust condition for fulfilled orders
);

This query uses a subquery to identify order_id of fulfilled orders and then deletes matching entries from OrderItems.

DELETE with EXISTS:

This approach uses the EXISTS operator to check if a related row exists in another table before deleting.

DELETE FROM Orders
WHERE EXISTS (SELECT 1 FROM Customers WHERE Orders.customer_id = Customers.customer_id AND Customers.customer_id = 10);

DELETE FROM Customers
WHERE customer_id = 10;

This checks if a customer with ID 10 exists before deleting their orders. It then deletes the customer themself.

Temporary Tables:

You can create a temporary table to store the IDs of rows to be deleted, then use that table in your DELETE statement.

This approach might be less efficient for large datasets but offers more flexibility in filtering conditions.

Choosing the right method:

• Temporary Tables: Useful for complex filtering or combining data from multiple tables.
• DELETE with EXISTS: Efficient for checking existence of related rows.
• Subqueries: Good for simple filtering based on another table.