Results 1 to 4 of 4

Difference between TRUNCATE, DELETE and DROP commands?

This is a discussion on Difference between TRUNCATE, DELETE and DROP commands? within the Programming forums, part of the Web Designing & Development category; Difference between TRUNCATE, DELETE and DROP commands?...

  1. #1
    Member
    Join Date
    Aug 2017
    Location
    New Delhi
    Posts
    77

    Default Difference between TRUNCATE, DELETE and DROP commands?

    Difference between TRUNCATE, DELETE and DROP commands?

  2. #2
    Senior Member
    Join Date
    Sep 2014
    Location
    India
    Posts
    161

    Default Re: Difference between TRUNCATE, DELETE and DROP commands?

    The DELETE command is used to remove some or all of the rows from a table. The WHERE clause can be used to delete only a few rows. If a WHERE condition is not specified, all rows are deleted. After the DELETE operation, you must start or run the transaction to make the change permanent or undo. Note that this will activate all DELETE triggers in the table.
    SQL> SELECT COUNT(*) FROM emp;
    COUNT(*)
    ----------
    14

    SQL> DELETE FROM emp WHERE job = 'CLERK';
    4 rows deleted.

    SQL> COMMIT;
    Commit complete.

    SQL> SELECT COUNT(*) FROM emp;
    COUNT(*)
    ----------
    10

  3. #3
    Senior Member RH-Calvin's Avatar
    Join Date
    Jun 2013
    Posts
    3,511

    Default Re: Difference between TRUNCATE, DELETE and DROP commands?

    - TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions.
    - To execute a DELETE queue, delete permissions are required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well.
    - DROP table query removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. DROP command requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.
    Cheap VPS | $1 VPS Hosting | Windows with Remote Desktop
    Cheap Dedicated Server | $29 Dedicated Server with Free IPMI Setup

  4. #4
    Senior Member
    Join Date
    Jan 2018
    Location
    PUNE
    Posts
    281

    Default Re: Difference between TRUNCATE, DELETE and DROP commands?

    The DROP command removes a table from the database. ... DROP and TRUNCATEare DDL commands, whereas DELETE is a DML command. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back