Solved

empty a table -  MySQL50

Posted on 2006-07-02
4
398 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
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 150 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 100 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now