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.
SQL> DELETE FROM emp WHERE job = 'CLERK';
4 rows deleted.
SQL> DELETE FROM emp
All row deleted.
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
SQL> TRUNCATE TABLE emp;
One more thing is DROP table
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.
SQL> DROP TABLE emp;
The main difference between delete and truncate is that
Deleted data can be rollbacked while truncated data can't
Hope this helps you
This is the very basic question and if you would search in our website, you would have got plenty of answers. So before posting anew question, you should search in the existing and if you are not satisfied with the answer then only you can write a new post. It is good for all of us to search the correct answer rather than getting the duplicate answers.
Anyway,Truncate used to delete all the row at a time and you cant use any condition like where clause.
Delete is used to delete row by row and you can apply the Where clause to filter and delete only the filtered rows.
Deleted row by using truncate cant be retrieved but deleted row using Delete command can be retrieved by using the OnDelete trigger.
Truncate is mainly used by DBA while delete is used by the developer.
Hope it will be helpful to you.
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
Detele is used remove one or more or entire rows from a table with where clause. After delete we can able to retrive it with the help of rollback.
delete from table1 where id =1 // For deleting one single row
delete from table1 // For deleting the whole
It is also used for removes all rows from the table. but the only one difference is we won't get back that deleted rows from the table
Truncate table table 1
difference between truncate and delete --
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.