Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Oracle - SQL - Delete, Drop, or Truncate ======

|**DELETE**|The DELETE command is used to remove rows from a table.|
|:::|A WHERE clause can be used to only remove some rows.|
|:::|If no WHERE condition is specified, all rows will be removed.|
|:::|After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.|
|:::|Note that this operation will cause all DELETE triggers on the table to fire.|
|**DROP**|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.|
|:::|The operation cannot be rolled back.|
|**TRUNCATE**|TRUNCATE removes all rows from a table.|
|:::|The operation cannot be rolled back and no triggers will be fired.|
|:::|TRUCATE is faster and does not use as much undo space as a DELETE.|

----

<WRAP group>
<WRAP half column>
**TRUNCATE**

<code sql>
BEGIN TRAN
TRUNCATE TABLE tranTest
SELECT * FROM tranTest
ROLLBACK
SELECT * FROM tranTest
</code>

<WRAP info>
**NOTE:**  

  * Truncate resets identity of table.
  * It locks the entire table.
  * It is DDL (Data Definition Language).
  * The WHERE clause cannot be used with it.
  * Trigger is not fired while truncate.

</WRAP>

</WRAP>

<WRAP half column>
**DELETE**

<code sql>
BEGIN TRAN
DELETE FROM tranTest
SELECT * FROM tranTest
ROLLBACK
SELECT * FROM tranTest
</code>

<WRAP info>
**NOTE:**  

  * Delete does not reset identity of table.
  * It locks the table row.
  * It is DML (Data Manipulation Language),
  * The WHERE clause can be used with it to filter data to delete.
  * Trigger is fired.

</WRAP>

**DELETE with WHERE**

<code sql>
DELETE FROM table_name
WHERE
  example_column_id IN (1,2,3) 
</code>

</WRAP>
</WRAP>

-----