How to delete rows from a table in MySQL 8

Introduction

Handling data efficiently often involves not only adding and updating records but also knowing how to remove them when they’re no longer needed. In MySQL 8, deleting rows from a table can be done using the DELETE statement. This tutorial will guide you through various scenarios in which you might want to delete rows, starting from the basic syntax to more advanced uses cases.

Prerequisites

Before you attempt to delete rows from a table in MySQL, ensure that you have the following:

• MySQL 8 installed and running on your machine.
• Access to a user account with sufficient privileges to perform data deletion.
• A database and table with some data you can afford to delete.

Basic DELETE Syntax

The basic syntax for the DELETE statement in MySQL is as follows:

DELETE FROM table_name WHERE condition;

Note: The WHERE clause specifies which record or records should be deleted. If you omit the WHERE clause, all records in the table will be deleted.

Deleting a Single Row

To delete a single row, you must provide a unique identifier for that row, typically the primary key.

DELETE FROM employees WHERE id = 1;

Assuming you had an employee with an ID of 1, this person’s record is now deleted from the employees table.

Deleting Multiple Rows

To delete multiple rows, you can expand the WHERE clause to include a broader condition.

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

This command would delete all orders placed before January 1, 2021.

Deleting Rows with Limit

MySQL 8 allows you to limit the number of rows to delete.

DELETE FROM comments WHERE user_id = 10 LIMIT 5;

This will delete up to 5 rows where the user_id is equal to 10.

Deleting Rows Using JOIN

Sometimes you may need to delete rows from one table based on a condition involving another table. MySQL 8 accommodates this with the use of JOINs within the DELETE statement.

DELETE products FROM products
JOIN product_categories ON products.category_id = product_categories.id
WHERE product_categories.name = 'Obsolete';

This will delete all products that have a category name ‘Obsolete’.

Important Considerations

• Transaction Management: For deletions that are potentially dangerous or need to be reversible, use transactions to safeguard your data integrity.
• Backup: Always backup your data before performing bulk deletions.

Using DELETE with Transactions

Here’s how you can use transactions to safely delete data:

START TRANSACTION;

DELETE FROM customers WHERE last_purchase < '2020-01-01';

-- Check if the delete was as expected
SELECT * FROM customers WHERE last_purchase < '2020-01-01';

-- If all is good:
COMMIT;

-- If something went wrong:
ROLLBACK;

Use COMMIT to make changes permanent or ROLLBACK to undo them if the delete didn’t go as planned.

Conclusion

In this tutorial, we’ve explored various ways to delete rows from a table in MySQL 8, ranging from simple row deletions to more complex operations involving JOINs. Always remember to perform deletions with caution, back up data regularly, and wrap operations in transactions where necessary.