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.

Chandu yadav

Updated on: 30-Jul-2019

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started