how can delete table data without deleting columnnames

Question

How can I delete table data, but not delete the table column names?

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

I have to delete all content of table but the table column names should not be deleted.

DELETE FROM table_name WHERE [condition];
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

score 15 · Answer 1 · edited Jun 24 '21 at 15:53

15

Just use

DELETE FROM table_name;

or

DELETE * FROM table_name;

If you still want to add condition, you can use following:

DELETE FROM table_name WHERE 1=1;

edited Jun 24 '21 at 15:53

Jaroslav Bezděk

6,967
6
29
46

answered Nov 17 '14 at 06:08

Sri Tirupathi Raju

779
4
14

1

I got an error with `DELETE * FROM table_name;` but the first option with the * works perfectly. Strange, but thanks. – Spinstaz Nov 12 '21 at 11:07

score 5 · Answer 2 · answered Nov 17 '14 at 06:11

5

This is equivalent to DELETE statement:

TRUNCATE TABLE yourTableName;

answered Nov 17 '14 at 06:11

Truncate is not transaction safe, will not invoke delete triggers,implicit commit,it is almost like drop and recreate. so better not to use. – Sri Tirupathi Raju Nov 17 '14 at 06:51
@TirupathiRaju There are cons and pros for DELETE, TRUNCATE and DROP and all of them depend on the implementations. – Nov 17 '14 at 07:13

score 4 · Answer 3 · 2014-11-17T06:51:18.473

Bibliography:
SQL - TRUNCATE TABLE Command http://goo.gl/YN7N9Y
SQL - Syntax http://goo.gl/viyYFU

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.

Syntax:

The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE  table_name;

Example: Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example to truncate:

SQL > TRUNCATE TABLE CUSTOMERS;

Now, CUSTOMERS table is truncated and following would be the output from SELECT statement:

SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)

Truncate is not transaction safe, will not invoke delete triggers,implicit commit,it is almost like drop and recreate. so better not to use. — Sri Tirupathi Raju, Nov 17 '14 at 06:54
@TirupathiRaju, truncate is used just for DB development case, avoiding production case, We could use it. :) — , Nov 17 '14 at 07:07
if that is the case it should be fine. But make a note even it will not give info on how many rows deleted. Ofcourse Truncate will happen few seconds when compared to delete row by row — Sri Tirupathi Raju, Nov 17 '14 at 07:11

score 1 · Answer 4 · edited May 23 '17 at 12:18

1

Just use this TRUNCATE TABLE table_name;

use below link mysql-delete-all-rows-from-table-and-reset-id-to-zero

edited May 23 '17 at 12:18

Community

1
1

answered Nov 17 '14 at 06:10

Sumit Jha

370
1
5
11

how can delete table data without deleting columnnames

4 Answers4