How can I delete rows from a MySQL table using id as a basis?

---

You can use the following methods in MySQL to delete rows from a table based on id values:

Method 1: Delete Row Equal to id

DELETE FROM mytable WHERE id=3;

Method 2: Delete Rows in Range

DELETE FROM mytable WHERE id BETWEEN 1 AND 3;

Method 3: Delete Rows in List

DELETE FROM mytable WHERE id IN (1, 4, 5);

Method 4: Delete Rows Greater Than  / Less Than Value

DELETE FROM mytable WHERE id > 4;

The following examples show how to use each method in practice with the following table named athletes in MySQL that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Celtics', 14);
INSERT INTO athletes VALUES (0003, 'Nuggets', 37);
INSERT INTO athletes VALUES (0004, 'Knicks', 19);
INSERT INTO athletes VALUES (0005, 'Warriors', 26);
INSERT INTO athletes VALUES (0006, 'Thunder', 40);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Celtics  |     14 |
|  3 | Nuggets  |     37 |
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Thunder  |     40 |
+----+----------+--------+

Let’s jump in!

Example 1: Delete Row Equal to id

We can use the following syntax to delete the row with an id value equal to 3:

DELETE FROM athletes WHERE id=3;

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Celtics  |     14 |
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Thunder  |     40 |
+----+----------+--------+

Example 2: Delete Rows in Range

We can use the following syntax to delete the rows with an id value between 1 and 3:

DELETE FROM athletes WHERE id BETWEEN 1 AND 3;

Output:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Thunder  |     40 |
+----+----------+--------+

Example 3: Delete Rows in List

We can use the following syntax to delete the rows with an id value in the list: 1, 4, 5:

DELETE FROM athletes WHERE id IN (1, 4, 5);

Output:

+----+---------+--------+
| id | team    | points |
+----+---------+--------+
|  2 | Celtics |     14 |
|  3 | Nuggets |     37 |
|  6 | Thunder |     40 |
+----+---------+--------+

Example 4: Delete Rows Greater Than  / Less Than Value

We can use the following syntax to delete the rows with an id value greater than 4:

DELETE FROM athletes WHERE id > 4;

Output:

+----+---------+--------+
| id | team    | points |
+----+---------+--------+
|  1 | Mavs    |     22 |
|  2 | Celtics |     14 |
|  3 | Nuggets |     37 |
|  4 | Knicks  |     19 |
+----+---------+--------+

Note: You could instead use >= if you would like to delete rows with an id value equal to or greater than a specific number.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Use DELETE with INNER JOIN