Safest way to empty table with mysql prompt

Question

Hi I have Amazon rds which i can connect to using the mysql prompt

I want to empty a table using the prompt command line

What's the best way to do the above? Thanks!

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

1

You can use the standard truncate command to empty the required tables. If you want to truncate multiple tables follow this question.

edited May 23 '17 at 12:08

Community

1
1

answered Dec 25 '16 at 07:36

chinoy

172
1
13

Hi is delete better or truncate? Also what's the format of the command? An example would be appreciated – user3236765 Dec 25 '16 at 08:12
Command is `TRUNCATE TABLE your_table_name`. In terms of speed truncate is faster. Both delete and truncate has its own set of rules. Please see [here](http://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql) for a comprehensive comparison between both commands. – chinoy Dec 25 '16 at 11:25

score 0 · Answer 2 · answered Dec 25 '16 at 07:35

0

Try

DELETE FROM `table_name`;

Notice the missing where clause, this will delete all rows.

answered Dec 25 '16 at 07:35

akuhn

27,477
2
76
91

Muhammad Muazzam · Answer 3 · 2016-12-25T08:29:33.790

0

Data can't be deleted that's connected by a constraint.

What you need is code that first removes constraints, then deletes the data, and finally restores the constraints:

See more at: http://www.devx.com/dbzone/Article/40967#sthash.NUmxsFr3.dpuf

Moreover, use DELETE command as you can ROLLBCK the operation. While using truncate, operation cannot be rolled back and no triggers will be fired

edited Dec 25 '16 at 08:29

answered Dec 25 '16 at 08:16

Muhammad Muazzam

2,810
6
33
62

Safest way to empty table with mysql prompt

3 Answers3