?
Solved

empty a table -  MySQL50

Posted on 2006-07-02
4
Medium Priority
?
406 Views
Last Modified: 2012-05-05
using MySQL 5.0    - - >

what is the easiest way to empty a table?   iow ~  to delete all the data that the table contains...     i'm not talking about dropping the table completely...   rather, just to empty (clear) all the data from it.

is there a way to to this with a "right"-click on the tablename after right-clicking and going to "Edit Table Data" ?    if not,  what is the SQL statement because   DELETE * FROM 'database'.'tablename'   doesn't seem to really empty the table..  cause when i go back to the MySQL Administrator and hit Refresh..  the data in the table still exists.      ??

Thanks.

0
Comment
Question by:lblinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 600 total points
ID: 17028242
truncate table `schemaName`.`tableName`;

This deletes the data and re-creates the index; this is the fastest and best method
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17028250
Delect * should work - unless you are using transactions; where you will need to commit them.

truncate keeps the table structure but removes all the data within.
0
 
LVL 4

Assisted Solution

by:JeePeeTee
JeePeeTee earned 400 total points
ID: 17036481
Truncate isn't transactional and can't be rolled-back when you have referantial constraints to this table and truncate them, your constaints will be removed.

 Delete from table is transactional. You can limit your rollback size by ussing delete from table where limit <your size>
0
 

Author Comment

by:lblinc
ID: 17038995
this worked...   thanks.

TRUNCATE `schemaName`.`tableName`;
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question