DELETE Statement in MariaDB: Removing Rows Effectively

Basic Structure

DELETE FROM table_name
[WHERE condition];

• WHERE condition (Optional): This clause allows you to specify which rows to delete based on certain criteria. If omitted, all rows in the table will be deleted.
• table_name: This is the name of the table you want to modify.
• FROM: This keyword specifies the table from which rows will be deleted.
• DELETE: This keyword initiates the delete operation.

Key functionalities

• Ordering Deletions (ORDER BY clause)
  (Not applicable for multiple-table DELETE) The ORDER BY clause arranges the rows in a specific order before deletion (ascending or descending), but doesn't necessarily affect which rows are deleted based on order.

  DELETE FROM products ORDER BY product_name DESC LIMIT 2;  -- Deletes the last 2 products alphabetically
  

• Limiting Deletions (LIMIT clause)
  (Not applicable for multiple-table DELETE) The LIMIT clause restricts the number of rows deleted. You specify the maximum number of rows to be affected.

  DELETE FROM orders LIMIT 5;  -- Deletes at most 5 rows from the orders table
  

• Filtering Rows (WHERE clause)
  The WHERE clause is crucial for targeted deletion. You can use comparison operators (=, >, <, etc.) and logical operators (AND, OR, NOT) to define the conditions for row selection. For example:

  DELETE FROM customers WHERE customer_id = 10;  -- Deletes the row with customer_id 10
  

Additional Points

• MariaDB also supports deleting rows from multiple tables in a single statement, but functionalities like LIMIT and ORDER BY are not available in this scenario.
• The DELETE statement returns the number of rows deleted. You can utilize the ROW_COUNT() function to retrieve this information.

---

1. Deleting a Single Row
   

This code removes the customer with ID 25 from the customers table.

DELETE FROM customers WHERE customer_id = 25;

1. Deleting All Rows Based on a Condition
   

This code deletes all orders placed before "2024-01-01" from the orders table.

DELETE FROM orders WHERE order_date < '2024-01-01';

1. Deleting All Rows from a Table
   

This code deletes all rows from the products table (use with caution!).

DELETE FROM products;

1. Deleting a Specific Number of Rows
   

This code deletes the first 3 entries (ordered by product ID) from the products table.

DELETE FROM products ORDER BY product_id LIMIT 3;

1. Deleting Rows from Multiple Tables (Without Filtering or Ordering)
   

This code deletes rows from the order_items table that correspond to deleted orders (assuming a foreign key relationship between orders and order_items).

DELETE FROM order_items
  INNER JOIN orders ON order_items.order_id = orders.id
WHERE orders.id IS NULL;

---

1. TRUNCATE TABLE
   

• Disadvantages
  
  • Cannot be rolled back (permanent deletion).
  • Doesn't trigger DELETE triggers on the table.
  • Not suitable for selective deletion based on conditions.
• Advantages
  
  • Significantly faster than DELETE, especially for large tables.
  • Resets auto-increment columns to 1.
• Functionality
  This statement removes all rows from a table, essentially making it empty.

Use TRUNCATE TABLE when you need to completely empty a table and don't require any rollback capabilities.

1. Dropping and Recreating the Table
   

• Disadvantages
  
  • Most resource-intensive option as it involves multiple operations.
  • Not suitable for frequent deletions on large tables.
• Advantages
  
  • Allows complete restructuring of the table if needed.
• Functionality
  This approach involves dropping the existing table and then recreating it with the desired data. You can achieve this by:
  1. Creating a temporary table with the desired structure.
  2. Inserting the rows you want to keep from the original table into the temporary table.
  3. Dropping the original table.
  4. Renaming the temporary table to the original table name.

Use this approach only if you need to restructure the table or for infrequent deletions on small tables.

• For restructuring or infrequent deletions on small tables, consider dropping and recreating.
• For fast and complete deletion without rollback, use TRUNCATE TABLE (if applicable).
• For selective deletion based on conditions and the ability to rollback, use DELETE.