How to Delete All Rows in MySQL Table Except Some

MySQL features DELETE statement to help you delete one or more rows in MySQL table. However, often you may need to delete all rows in MySQL table except some. In this article, we will learn how to do this in MySQL.

How to Delete All Rows in MySQL Table Except Some

Let us say you have the following MySQL table.

mysql> create table sales(id int, created_at datetime, amount int);

mysql> insert into sales(id, created_at, amount) 
       values(1, '2022-11-15 03:00:00', 180),
       (2, '2022-11-16 05:30:00', 100),
       (3, '2022-11-17 04:15:00', 280),
       (4, '2022-11-18 02:40:00', 150);

mysql> select * from sales;
+------+---------------------+--------+
| id   | created_at          | amount |
+------+---------------------+--------+
|    1 | 2022-11-15 03:00:00 |    180 |
|    2 | 2022-11-16 05:30:00 |    100 |
|    3 | 2022-11-17 04:15:00 |    280 |
|    4 | 2022-11-18 02:40:00 |    150 |
+------+---------------------+--------+

Let us say you want to delete all rows except the one with ID=3. You can do this using NOT IN operator as shown below.

delete from table_name where column_name NOT
IN(‘value1’,‘value2’,‘value3’,.........N);

The above query will delete all values except the ones where column name is not among the ones mentioned in NOT IN clause. Applying the above syntax to our table,

delete from sales where id NOT IN (3);

Alternatively, you can also use <> operator to exclude rows where column matches specific value.

delete from table_name where column_name <> 'value';

Using the above in our case

delete from sales where id <> 3;

Similarly, you can also use NOT EQUAL TO (!=) operator to achieve the same result.

delete from sales where id != 3;

Both <> and != give same result it is just that <> is the SQL standard and that != is non-standard but still implemented by most databases.

If you are excluding string values, you can also use NOT LIKE operator.

delete from sales where id NOT LIKE '3';

You can also use wildcard % to match multiple values. Here is the query to exclude all rows where id contains ‘3’ in its value.

delete from sales where id NOT LIKE '%3%';

In this article, we have learnt several different ways to delete all rows from MySQL table except a few. You can use any of the above mentioned operator as per your requirement.

Also read:

How to Fix Unknown Column in Field List
How to Loop Through All Rows of Table in MySQL
How to Combine Columns in MySQL
How to Get List of Stored Procedure & Functions in MySQL
How to Select Rows Where Date Matches Day in MySQL