- Trending Categories
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
Truncate with condition in MySQL?
In MySQL, there is no way to truncate with condition. You cannot use truncate statement with where clause.
If you want the condition, use delete command −
DELETE FROM yourTableName WHERE youCondition;
The above syntax is fine but if you want a faster solution, then DELETE is not good in comparison to Truncate. The advantage with truncate is that it does not write to the logs.
Let us create a table. The query to create a table is as follows −
mysql> create table DeleteDemo -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (1.22 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into DeleteDemo values(101,'Carol'); Query OK, 1 row affected (0.15 sec) mysql> insert into DeleteDemo values(102,'Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DeleteDemo values(103,'Bob'); Query OK, 1 row affected (0.16 sec) mysql> insert into DeleteDemo values(104,'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DeleteDemo values(105,'John'); Query OK, 1 row affected (0.09 sec) mysql> insert into DeleteDemo values(106,'Maria'); Query OK, 1 row affected (0.20 sec) mysql> insert into DeleteDemo values(107,'Johnson'); Query OK, 1 row affected (0.17 sec)
Let us now display all records from the table using select command. The query is as follows −
mysql> select *from DeleteDemo;
Output
+------+---------+ | Id | Name | +------+---------+ | 101 | Carol | | 102 | Sam | | 103 | Bob | | 104 | Mike | | 105 | John | | 106 | Maria | | 107 | Johnson | +------+---------+ 7 rows in set (0.00 sec)
Now you can use delete command but that won’t truncate with where clause. The query to delete records from the table using where clause is as follows −
mysql> delete from DeleteDemo where Id>104; Query OK, 3 rows affected (0.13 sec)
Let us check the table data once again using select command. The query is as follows −
mysql> select *from DeleteDemo;
Output
+------+-------+ | Id | Name | +------+-------+ | 101 | Carol | | 102 | Sam | | 103 | Bob | | 104 | Mike | +------+-------+ 4 rows in set (0.00 sec)
Look at the above sample output, all records greater than 104 is deleted from the table.
1K+ Views
