Filtering and Deleting in MariaDB: A Guide to the DELETE ... WHERE Clause

For example, let's say you have a table named customers with columns for id, name, and city. You want to delete all customers from the city "New York". Here's the DELETE statement for that:

DELETE FROM customers
WHERE city = "New York";

In this example:

• DELETE FROM customers specifies the table to delete from.
• WHERE city = "New York" is the condition that filters the rows. It will only delete rows where the city column value is "New York".

Important Considerations:

• Be cautious when deleting rows, especially without a specific WHERE clause. Deleting all rows from a table is faster achieved using TRUNCATE TABLE (assuming no constraints or locks prevent it).
• While you can reference the same table in the DELETE and WHERE clause, MariaDB doesn't support using a subquery that selects from the same table within the DELETE statement.

DELETE FROM customers
WHERE city = "New York";

This code deletes all customers from the "customers" table where the "city" column value is "New York".

Deleting duplicate entries:

DELETE c1
FROM customers c1
INNER JOIN customers c2 ON c1.id > c2.id  -- This ensures we only delete the higher ID duplicate
WHERE c1.name = c2.name AND c1.email = c2.email;

This code deletes duplicate entries from the "customers" table based on matching "name" and "email" columns. It uses a self-join to compare rows within the same table. The id comparison ensures we only delete the record with the higher ID (assuming IDs are unique and increasing).

DELETE FROM products
WHERE price < 10 OR price > 50;

This code deletes products from the "products" table where the "price" column value is less than 10 or greater than 50.

• This is a faster way to remove all rows from a table, especially for large datasets.
• However, unlike DELETE, TRUNCATE TABLE doesn't trigger database triggers or logs (unless configured otherwise).
• Use this with caution as it's not recoverable.

Temporary Tables and DELETE...SELECT:

• Create a temporary table to hold the rows you want to keep.
• Use a SELECT statement to filter the desired rows from the original table into the temporary table.
• Truncate the original table and then insert all rows from the temporary table back into the original table.

Stored Procedures:

• For complex deletion logic or repetitive tasks, you can create a stored procedure that encapsulates the deletion logic.
• This can improve code reusability and maintainability.

Here's a brief comparison: