What is the difference between DROP,DELETE and TRUNCATE.. Can anyone tell why we require EXISTS with example.

Showing Answers 1 - 13 of 13 Answers

Hi Abhi,

Drop and Truncate are DDL, With Drop Command we can remove entire Table or columns from database. With Truncate we can remove the records in the table by keeping table structure.

Drop and Truncate are AutoCommit.

By using Delete command we can remove rows in the table but its not autocommit

  Was this answer useful?  Yes

Lalita.p

  • Sep 29th, 2006
 

Hi

Delete Command removes all the rows from the table and the deleted record get logged into transaction log which slow down the performance. While truncate table also deletes the records from the table but does not log the deleted record. Records deleted with truncate table can be autocomitted.

Drop table is used to drop the table.

Delete table deletes rows as well as remove the table skeleton. while truncate table only removes rows from the table.

  Was this answer useful?  Yes

Hi By using DROP we can delete the table(which includes records) from the Database.However by using TRUNCATE we can delete the records in the table permanently.DELETE comand is also used to delete the records from the table.The difference is using DELETE we can retrieve datas using ROLLBACK but this can't be done in the case of TRUNCATE.

  Was this answer useful?  Yes

Delete is a DML(Data Manipulation Language). Delete command is deletes the record from the existing table.The systax for Delete is

1)sql> Delete from <Table_Name>.

This Deletes All the Record From The TAble

2) sql> Delete from <Table_Name>

   Where <condition_Statment>

This deletes a perticular set of record.

Note:Delete is not Autocommit Statment(Infact None os the DML are auto commit)

Drop and Truncate both are DDL(Data Definition Language).

Drop {Delete or drops} the table with it's structure. It is as autocommit statment.Drops Once fired can not be rolled back.

syntax:

   sql>drop table <Table_Name>

Trucate is the command used to delete all record from table. but the structure of the table remain same.It is also a autocommit statment.

syntax;

sql>truncate table <Table_name>

  Was this answer useful?  Yes

suresh kumar chappali

  • Nov 9th, 2006
 

Delete:It's an DML command which is used for deleting particular table in the database...even though we delete the data we can regain it by rollback command.

Example:SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELESQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

Truncate:This command is a DDL Command which is used to delete a table...I Just Conclude it as Truncate=Delete+Commit...once u applied this on table u can't get the data back...but the structure will be there..

Example:

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
         0

Drop:It is used to destroy entire structure of a table...once we apply this command on any particular table we can't get it in anyway...so be cautious while applying this command.

Example:

SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

  Was this answer useful?  Yes

Pawan Ahuja

  • Dec 4th, 2006
 

Drop means we can delete the table from database,Delete means we can delete the record from table bcz it's a DML command we can also rollback.

but in truncate we can not rollback the records.

Exists Command:

Example:

select * from emp where sal in(select sal from emp)

exists means

select * from emp where exists(select sal from emp)

Regards

Pawan Ahuja

  Was this answer useful?  Yes

shelkumar handa

  • Feb 7th, 2007
 

Hi to all of you
Drop command generally removes the table element, but another copy of record is also present in oracle server engine. When you issue delete command it removes the record from the table but the memory space that is taken by the record is not deleted that is why using ROLLBACK command that records are returned. But in case of TRUNCATE command memory taken by record is also erased and hence ROLLBACK command will not work, so after using TRUNCATE command will give better performance in oracle server.

  Was this answer useful?  Yes

subrahmanyam

  • Feb 10th, 2007
 

Guys,Everyone here is correct.But you missed another important point here.TRUNCATE WILL NOT FIRE DELETE TRIGGERThank you

  Was this answer useful?  Yes

velsowmya

  • Aug 7th, 2007
 

Delete is a DML command.we can remove more than one row using delete command
Truncate is used for deleting the entire rows in the table.we can get the data back.where as in delete we can rollback data.
Drop cammand is used for dropping the entire tabel

  Was this answer useful?  Yes

Guru Shetty

  • Aug 7th, 2007
 

Drop command is used 2 drop the whole table;
It will release the space which is occupied by the database.

Through delete command
you can delete the records as well as you can rollback also.
Autocommit is not in built here but it will not release the space viz is occupied.
For e.g
you have a table of 50MB & you have deleted 50% of its records from the table then your table size should be 25MB. But it will not be so in delete case, it will be around 45MB After deleting because it will not release the space.
You can also give conditions for delete.

Truncate removes all the records from a table releasing space also.

  Was this answer useful?  Yes

lakan reddy

  • Sep 21st, 2012
 

delete:delets the records from the table by using where claus
truncate:removes all the records from the table and frees the space containing by the table
drop:drops the table and its structure

  Was this answer useful?  Yes

Nazeera Jaffar

  • Sep 26th, 2012
 

Delete:
Deletes the contents of the table.Also allows us to delete particular row(s) by condition.We can undo the delete operation and write trigger on delete action.
Truncate:
Delete the contents of the table leaving the structure.we Cannot undo or use condition,triggers .
Drop:
Delete the structure as well as the entire table from the database. We can undo the delete operation and write trigger on delete action.

  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.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions