Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between DELETE and TRUNCATE in MySQL.

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 131
    Comment on it

    In MySQL, DELETE and TRUNCATE both are used for deleting data from table.

    DELETE

    • DELETE comes under DML(Data Manipulation Language).
    • DELETE can be used to delete a particular row by using WHERE clause.
    • It is slower than TRUNCATE as it keeps logs.
    • Rollback is possible when used with TRANSACTION.
    • If there is a primary key with auto-incremt in the table then DELETE will not reset the counter (means PK for new record will start from the last counter)

    If we want to delete a record from table then we use DELETE FROM statement.

    Syntax:

    Below statement deletes rows from table on the basis of where condition, we can define any condition in the WHERE clause:

    DELETE FROM table_name [WHERE Clause]
    

    If WHERE clause is not specified with the statement then all the records will be deleted from the given table.

    Example:

    We have a table with the following values.

    user
    id user_name country
    .......................................
    1 John Canada
    2 Chris America
    3 Joy London
    4 Jenny Korea 
    

    DELETE Examples

    Delete the user where id is 4:

    DELETE FROM user where id = 4;
    

    Result

    user
    id user_name country
    .......................................
    1 John Canada
    2 Chris America
    3 Joy London
    

    Below statement deletes all the records from the table:

    DELETE FROM table_name;
    

    Example:

    DELETE FROM user;
    

    TRUNCATE

    • TRUNCATE comes under DDL(Data Definition Language).
    • Cannot use WHERE clause.
    • It is faster than DELETE as it does not keep logs.
    • Rollback is possible when used with TRANSACTION.
    • If there is a primary key with auto-incremt in the table then TRUNCATE will reset the counter (means PK for new record will start from 1)

    TRUNCATE TABLE statement is usually used to delete all the records inside a table.

    The TRUNCATE TABLE Statement

    When we want to delete all the records inside a table then, use the TRUNCATE TABLE statement.

    Syntax:

    TRUNCATE TABLE table_name;
    

    Example:

    The below statement deletes all the records of "user" table:

    TRUNCATE TABLE user;
    

    Hope this will help you :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: