MySQL – Delete Rows in Table – Examples

MySQL – Delete Rows in Table

The DELETE statement in MySQL is used to remove rows from a table. You can delete specific rows based on conditions or, if necessary, delete all rows in a table.

This tutorial will guide you through the steps to delete rows, starting from creating a database and inserting data for examples, followed by different ways to use DELETE.

Step 1: Create a Database

To start, we need a database to store our table. Use the CREATE DATABASE command to create a new database.

CREATE DATABASE school;

This command creates a database named school. You can change the name if needed.

Step 2: Select the Database

After creating the database, use the USE command to select it so we can create tables and work with it.

USE school;

Step 3: Create a Table

Now, let’s create a students table with columns for storing student details.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    rollno INT,
    age INT,
    class VARCHAR(10)
);

This command creates a students table with columns for ID, name, roll number, age, and class.

Step 4: Insert Data into the Table

Next, we insert some sample data into the students table to use in our SELECT examples.

INSERT INTO students (name, rollno, age, class) VALUES ('Arjun', 14, 20, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Raja', 15, 21, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Prasanth', 16, 22, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Hussain', 17, 23, '10C');
INSERT INTO students (name, rollno, age, class) VALUES ('Sai', 18, 20, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Pranathi', 19, 21, '10A');

These commands insert six rows of data into the students table with details for each student.

Step 5: Delete Rows from the Table

Now that we have data in the table, we can use the DELETE statement to remove rows. Here are different ways to use DELETE in MySQL:

Example 1: Delete a Specific Row

To delete a specific row, use the DELETE statement with a WHERE clause to specify the condition. For example, to delete the student with rollno 14:

DELETE FROM students
WHERE rollno = 14;

This command deletes the row where rollno is 14, removing the student “Arjun” from the table.

Example 2: Delete Multiple Rows Based on a Condition

To delete multiple rows, specify a condition that matches several records. For example, to delete all students with an age of 20:

DELETE FROM students
WHERE age = 20;

This command deletes all rows where age is 20, which will remove both “Sai” and any other students with that age.

Example 3: Delete All Rows in a Table (Use with Caution)

If you need to delete all rows in a table, use the DELETE statement without a WHERE clause. Be careful, as this will remove all data from the table.

DELETE FROM students;

This command deletes all rows in the students table. The table structure remains, but it will be empty.

Example 4: Truncate Table to Delete All Rows Quickly

The TRUNCATE statement is an alternative to DELETE for removing all rows. It is faster and automatically resets any auto-increment columns.

TRUNCATE TABLE students;

This command removes all rows from the students table and resets the auto-increment counter. Note that TRUNCATE cannot be used with a WHERE clause and is typically faster than DELETE.

Conclusion

In MySQL, the DELETE statement is a powerful tool for removing rows from a table based on specific conditions. Use it carefully, especially when deleting multiple or all rows, to prevent accidental data loss. For quickly removing all rows, consider using the TRUNCATE statement, which resets the table while retaining its structure.