Thread: Deleting Info

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Randolph, NJ
    Posts
    36
    Rep Power
    15
    I want to keep my table in my database, but I want to delete all entries because they were just tests. How would I be able to delete the entries, and not the whole table. Thank you in advance.

    ------------------
    ================
    -Khill, www.tsrealm.com
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    > delete from table_name;

    You can't remove a table in the client app by using "delete" so you can monkey around with it without worrying about removing your actual table...you'd have to use "drop table table_name" to remove the whole table
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    14
    Rep Power
    0
    Go to http://www.mysql.com/Manual_chapter/...section=DELETE

    which reads:

    DELETE [LOW_PRIORITY] FROM tbl_name
    [WHERE where_definition] [LIMIT rows]


    DELETE deletes rows from tbl_name that satisfy the condition given by where_definition, and returns the number of
    records deleted.

    If you issue a DELETE with no WHERE clause, all rows are deleted. MySQL does this by recreating the table as an empty table,
    which is much faster than deleting each row. In this case, DELETE returns zero as the number of affected records. (MySQL can't
    return the number of rows that were actually deleted, because the recreate is done without opening the data files. As long as the
    table definition file `tbl_name.frm' is valid, the table can be recreated this way, even if the data or index files have become
    corrupted.).

    If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty,
    you can use a DELETE statement of this form:

    mysql> DELETE FROM tbl_name WHERE 1>0;

    Note that this is MUCH slower than DELETE FROM tbl_name with no WHERE clause, because it deletes rows one at a time.

    If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.

    Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused
    space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE
    TABLE is easier, but myisamchk is faster. See section 7.10 OPTIMIZE TABLE syntax, and section 15.4.3 Table optimization.

    The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control
    is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply
    repeat the DELETE command until the number of affected rows is less than the LIMIT value.

  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Randolph, NJ
    Posts
    36
    Rep Power
    15
    Thanks a lot guys. I deleted the whole table because it was all a test. But now I know I can delete specific rows in case some idiot posts something stupid. Thanks.

    ------------------
    ================
    -Khill, www.tsrealm.com

Similar Threads

  1. Getting database info based on db info
    By Zamees in forum PHP Development
    Replies: 3
    Last Post: February 2nd, 2004, 08:55 PM
  2. How can I start mySQL server
    By jyu1 in forum MySQL Help
    Replies: 2
    Last Post: September 5th, 2003, 11:06 AM
  3. Deleting database info by date
    By Styrok in forum Perl Programming
    Replies: 2
    Last Post: July 3rd, 2003, 03:34 AM
  4. Migrating mysql info into Excel table...
    By preemi in forum MySQL Help
    Replies: 4
    Last Post: May 11th, 2002, 09:11 AM
  5. Overwriting info in database
    By hccfilms in forum PHP Development
    Replies: 8
    Last Post: March 13th, 2002, 10:54 AM

IMN logo majestic logo threadwatch logo seochat tools logo