1

*Delete is a DML(Data Manipulation Language). Delete command deletes records from the existing table.*The syntax for Delete is

sql> Delete from .

This Deletes All the Record From The TAble

sql> Delete from
      Where  **

This deletes a particular set of records.

Note:Delete is not Autocommit Statement(In fact none of 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 autocommit statement. Drops Once fired can not be rolled back.

syntax:

sql>drop table

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

syntax;

sql>truncate table**

Can we rollback the truncate?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
anil kumar
  • 55
  • 1
  • 1
  • 8
  • 3
    This question, and its title, are a mess. Please clean them up. – David Aldridge Sep 07 '13 at 13:12
  • Oracle provides extensive online documentation, complete with examples, of all their products. I suggest that you consult the manuals appropriate for the version of Oracle you're using, read up on these commands, and try out the examples. In this way you will gain valuable experience using these commands, experience that you otherwise will be unable to acquire. Best of luck. – Bob Jarvis - Слава Україні Sep 07 '13 at 21:25

3 Answers3

12

DELETE handles rows chosen with a WHERE statement. Its use is part of the discipline of running production applications. For example, you might DELETE all rows that have been marked "complete" more than a month ago.

DELETE can be done as part of a transaction. That is, DELETE operations can be committed or rolled back.

TRUNCATE rapidly removes all rows from a table while maintaining the table definition. (Row by row DELETE can take time, especially for tables with lots of keys.) It comes in handy for such things as log tables that start out empty for each week's production. It has the convenient side effect of resetting the indexes and releasing unused disk storage. I have used TRUNCATE to wipe out the contents of a log table that used to contain millions of rows, to switch to an operational discipline where it only contains a weeks' worth of rows, for example.

DROP rapidly removes all rows and the table's definition. It's used most often in connection with setting up a DBMS schema for operations or migrating data to a new server.

TRUNCATE and DROP are considered data definition statements. As such they can't be part of transactions, and can't be rolled back.

If you find yourself using TRUNCATE or DROP in routine production, you should be careful to understand why you're doing so. They are blunt instruments compared to DELETE.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I heavily enjoyed your answer,as you have told each command's definition and practical place where I can think of them to use. But I would expect some more texts from you about the `DROP` commands practical application area. – Arup Rakshit Sep 07 '13 at 13:24
  • @Babai:- Check out this:- http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9004.htm This will surely fill the rest part which you are looking for!!!! – Rahul Tripathi Sep 07 '13 at 13:31
  • @RahulTripathi Can you help me to install Oracle11g in Ubuntu OS ? I used it in windows but no idea about how should I proceed in Ubuntu. – Arup Rakshit Sep 07 '13 at 13:38
  • @Babai:- Although I would have loved to see this as a seperate question. But you can check this if that helps:- https://forums.oracle.com/thread/2461988?start=0&tstart=0 – Rahul Tripathi Sep 07 '13 at 13:39
1

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows.

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired.

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired.

Check this out,

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

"Drop" removes the table completely. It no longer exists.

"Truncate" is a fast way of removing all the rows from the table.

"Delete" removes rows that match a condition.

see also: What's the difference between TRUNCATE and DELETE in SQL

Community
  • 1
  • 1
David Aldridge
  • 51,479
  • 8
  • 68
  • 96