Differences

This shows you the differences between two versions of the page.

--- oracle:sql:delete_drop_or_truncate [2021/08/08 16:33] – peter
+++ oracle:sql:delete_drop_or_truncate [2021/08/08 17:53] (current) – peter
@@ Line 1: / Line 1: @@
 ====== Oracle - SQL - Delete, Drop, or Truncate ======
-|DELETE|The DELETE command is used to remove rows from a table.|
+|**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.|
+|**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.|
+|**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>
+-----