Differences

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

--- oracle:sql:delete_drop_or_truncate [2021/08/08 16:36] – 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.|
@@ Line 18: / Line 18: @@
 <WRAP group>
 <WRAP half column>
-TRUNCATE
+**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
+**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>
+-----
-^TRUNCATE^DELETE^
-|BEGIN TRAN|BEGIN TRAN|
-|TRUNCATE TABLE tranTest|DELETE FROM tranTest|
-|SELECT * FROM tranTest|SELECT * FROM tranTest|
-ROLLBACK 	ROLLBACK
-SELECT * FROM tranTest 	SELECT * FROM tranTest
-Truncate reset identity of table. 	Delete does not reset identity of table.
-It locks the entire table. 	It locks the table row.
-Its DDL(Data Definition Language) 	Its DML(Data Manipulation Language)
-We can't use WHERE clause with it. 	We can use WHERE to filter data to delete.
-Trigger is not fired while truncate. 	Trigger is fired.
-Syntax : 	Syntax :
-TRUNCATE TABLE table_name 	1) DELETE FROM table_name
-) DELETE FROM table_name WHERE
-	example_column_id IN (1,2,3)