Sql: Difference between truncate and delete command

babloo0311

@babloo0311-J22PBc • Oct 26, 2024

Oct 26, 2024

1.6K

Hi friends,

what is the difference between delete and trancate command

Like 0 Replies 12

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

CrazyEngineers powered by Jatra Community Platform

Kaustubh Katdare

@thebigk • Nov 25, 2008

babloo0311
Hi friends,

what is the difference between delete and trancate command
Its 'truncate', I think.
0
manupep

@manupep-fYm2v7 • Nov 25, 2008

Is there any command 'delete' ?
I think 'drop table' is the command used to delete one table.
'truncate' will delete only the contents of the table . The table structure should remain. we can add datas further.
0
Prasad Ajinkya

@prasad-aSUfhP • Nov 25, 2008

Well, you can also use delete from <table_name> command to delete only the contents of the table. Schema will remain intact though.
0
manupep

@manupep-fYm2v7 • Nov 26, 2008

oh! yes 'delete' is there to delete rows according to condition.
0
shalini_goel14

@shalini-goel14-ASmC2J • Nov 26, 2008

Hi Babloo

1. TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

2. TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

3. Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it.
Truncate - delete all records from table There is no rollback it always commit without giving the commit command

TRUNCATE is a DDL command and cannot be rolled back and All of the memory space is released back to the server. It can not use the Where conditions. DELETE is a DML command and can be rolled back. here can be use where conditions. TRUNCATE is much faster but don not use it if delete can work.
0
babloo0311

@babloo0311-J22PBc • Nov 26, 2008

yeah thanks buddy
0
manupep

@manupep-fYm2v7 • Nov 26, 2008

oh! so much infos .Thanks shalini.
0
shalini_goel14

@shalini-goel14-ASmC2J • Nov 27, 2008

Always welcome 😀 .

Sorry for Duplicate points:

1. TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

2. TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

0
babloo0311

@babloo0311-J22PBc • Nov 27, 2008

yeah no probs
0
Prasad Ajinkya

@prasad-aSUfhP • Nov 27, 2008

Wow! Good tips Shalini!!
0
zia.sepsis

@ziasepsis-0NGPzn • Dec 30, 2008

Truncate table does not support the Where clause. In addition, the Truncate table statement does not fire a trigger. When truncate is used, the deleted rows are not entered in the transaction log. there fore it can't be rolled back. when the delete command is used , it creates a transaction log file which help you in the process of rollback.
the syntax of truncate statement is:-
truncate table table_name
the syntax of delete statement is:-
delete table_name

both the statements is used to delete all the records form the table
correct me if am wrong 😁
0
HEART-HACKER

@heart-hacker-nZz25T • Dec 30, 2008

I think you got your ANS 😁
0