MySQL DELETE Row(s) Statement

MySQL DELETE ROW(s) Statement

The MySQL DELETE statement is used to remove rows from a table. This statement is essential for managing and maintaining data integrity by removing unwanted or obsolete records.

---

Syntax

DELETE FROM table_name
WHERE condition;

The DELETE statement has the following components:

• table_name: The name of the table from which to delete the rows.
• condition: The condition that specifies which rows to delete. If no condition is specified, all rows in the table will be deleted.

---

Example MySQL DELETE ROW(s) Statement

Let's look at some examples of the MySQL DELETE statement:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a table named employees with columns for id, first_name, last_name, and email.

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com');

This query inserts two rows into the employees table.

Step 4: Deleting a Single Row

Delete a single row from the table:

DELETE FROM employees
WHERE first_name = 'John' AND last_name = 'Doe';

This query deletes the row where the first_name is 'John' and the last_name is 'Doe' from the employees table.

Step 5: Deleting Multiple Rows

Delete multiple rows from the table:

DELETE FROM employees
WHERE last_name = 'Doe';

This query deletes all rows where the last_name is 'Doe' from the employees table.

Step 6: Verifying the Deletions

To verify that the rows have been deleted, you can select all rows from the table:

SELECT * 
FROM employees;

This query retrieves all rows from the employees table. The result will show the remaining rows after the deletions.

---

Conclusion

The MySQL DELETE statement is a powerful tool for removing rows from a table. Understanding how to use the DELETE statement is essential for effective data management and maintenance in MySQL.