How many ways you can delete the table records ?

In how many ways can I delete all the contents of a table and the entire table. Explain with syntax ?

Questions by g.ashok1988

Showing Answers 1 - 11 of 11 Answers


  • Jul 17th, 2008

To delete the records in a table , there are 2 ways .
1.Using the delete statement
2.using the truncate statement .
To delete the entire table drop statement is used .

Deleting a record from a table :
use the following statement.
delete from table_name
where column_name = some_value; .
The above statement deletes a single record at a time.

To delete all the records in a table the following statement is used.
delete * from table_name;

coming to the truncate statement , its like a delete statement without a where clause. the syntax is
Truncate table table_name.
It also removes all the records in the table.
In case of delete * from table_name and truncate table table_name all the records in the table is deleted but the table structure and its columns , constraints , indexes remain the same .

To remove the table from the database,use the drop table statement.
drop table table_name;

There are two ways To delete all records in the table.
1. using delete
2. using truncate
In delete option we have used drop command. eg. drop table table_name;
for second one eg. truncate tablename; Both are delete the records in a table.

  Was this answer useful?  Yes


  • Apr 25th, 2009

There are two way to delete table record.
If you want to delete specify record to use delete command delete table table_name

but do you want to delete all the table record to use truncate table_name.

  Was this answer useful?  Yes

We have 2 options :

1. Delete

Delete : It is DML concepts.
Syntax: Delete from <table_name>;
Example: Delete from emp;
Explain: The above query will delete all the record from the tale. Supose need
to delete the particular record from the table ,we can give the condition.

Syntax: Delete from <table_name> where <condition>
Example: Delete from emp where emp_no =101;
Explain: By default You gave the condition 101 instead of 1001. You can Rollback
and get the 101 record before doing commit.

Truncate : It is DDL commend.
Syntax: Truncate Table <Table_Name>;
Example: Truncate Table Emp;
Explain: The above query will delete all the record from the table. Suppose need
to delete the particular record from the table, we cannot give the condition.
Once we truncate the table we cannot Rollback.

DROP : Remove the table from data base.
Syntax: Drop Table <Table_Name>
Example : Drop Table Emp;

If you want to delete only few rows from Table then use

Delete Table <Tablename>
where <condiition>

It will just delete the data but not unhold the space.

If you want to remove whole data then
Truncate table <Tablename>
will remove all rows and unhold the space.

Delete Table <Tablename> will also do that but it won't unhold the space.

  Was this answer useful?  Yes

Two ways

BY using delete----
U can delete one nd all rows by using delete command
        delete from tablename where column_name=column_value;  
                (it will delete the row related to given column value)
        delete * from tablename;
                 (it will remove all the rows from the table)
NOTE-1- In case of delete stil high water mark remain set to same position even   
               after deleteing all the rows.
          2- Also the rows never release the memory space .
          3- U can rollback in case of delete.

BY using trancate-----
U can delete all the rows by using the trancate command.
        trancate table tablename;

NOTE- 1- in case of trancate high water mark get set to zero.
           2- all the rows get deleted nd relase the memory space but structure,
                index nd constraint remain the same.
           3- U can never rollback in case of trancate command.

  Was this answer useful?  Yes


  • Sep 30th, 2009

Actually there are three ways to delete a table

1. Delete: This is used to delete the one or all the records. The thing with this is that only data is been deleted but the table structure and the memory allocated to it is safe.

2. Truncate: Truncate is similar to Delete, the only difference is that it keeps the structure but removes the memory allocated to the table.

3. Drop: This is used to delete the entire table along with the table structure and memory allocated to it.

  Was this answer useful?  Yes


  • Mar 5th, 2010

I would like to answer this question different way !!

you can also delete records using following steps.

1. create temp table excluding records need to be deleted.
2. drop original table
3. rename temp table to original table.

Pro : this approach is very effective when you are deleting records from huge table having number of records in milions.

cons: If your Original table consists objects like indexes, constraints , sequences etc., you may loose them.

mayank kapoor

  Was this answer useful?  Yes

There are two(2) ways to delete recordes from a table are as follows:

            1. Delete a specific record from table.
            2. delete entire recordes of table.

     Syntex for deleting a specific record from the table:
          DELETE FROM <Table_name>
          WHERE <column_name> Operateor <value>;

     Syntex for deleting entire records from the table:

       1.   DELETE FROM <table_name>;

        2.  TURNCATE TABLE <Table_name>;

  Was this answer useful?  Yes

we delete record in a table  2 ways using delete command
    syntax:delet from table name where condition.
    example:delete from emp where eno=1; using turncate command

    syntax:turncate table tablename
    example:turncate table emp;        

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions