Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to delete all rows except some in MySQL?
You can use NOT IN operator for the rows you do not want to delete. Following is the syntax −
delete from yourTableName where yourColumnName NOT IN(‘yourValue1’,‘yourValue2’,‘yourValue3’,.........N);
Let us first create a table −
mysql> create table deleteAllRowsWithCondition -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.84 sec)
Following is the query to insert some records in the table using insert command −
mysql> insert into deleteAllRowsWithCondition(Name) values('Larry');
Query OK, 1 row affected (0.14 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('John');
Query OK, 1 row affected (0.21 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('Sam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('Mike');
Query OK, 1 row affected (0.16 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('Bob');
Query OK, 1 row affected (0.06 sec)
mysql> insert into deleteAllRowsWithCondition(Name) values('David');
Query OK, 1 row affected (0.14 sec)Following is the query to display all records from the table using select statement −
mysql> select * from deleteAllRowsWithCondition;
This will produce the following output −
+----+-------+ | Id | Name | +----+-------+ | 1 | Larry | | 2 | John | | 3 | Sam | | 4 | Mike | | 5 | Carol | | 6 | Bob | | 7 | David | +----+-------+ 7 rows in set (0.00 sec)
Here is the query to delete all rows with some condition. We are not deleting 'John','Mike',and 'Carol' here −
mysql> delete from deleteAllRowsWithCondition where Name NOT IN('John','Mike','Carol');
Query OK, 4 rows affected (0.15 sec)Let us check whether some rows have been deleted from the table or not. Following is the query −
mysql> select * from deleteAllRowsWithCondition;
This will produce the following output −
+----+-------+ | Id | Name | +----+-------+ | 2 | John | | 4 | Mike | | 5 | Carol | +----+-------+ 3 rows in set (0.00 sec)
Updated on: 30-Jul-2019
5K+ Views
Advertisements