DELETE and TRUNCATE commands

 

What is the difference between DELETE and TRUNCATE commands?

• DELETE: This query is used to delete or remove one or more existing tables.
• TRUNCATE: This statement deletes all the data from inside a table.

The difference between DELETE and TRUNCATE commands are as follows:

• TRUNCATE is a DDL command, and DELETE is a DML command.
• With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger.
• If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

The syntax for the DELETE command:

DELETE FROM table_name
[WHERE condition];

Example:

select * from stu

Output:

delete from stu where s_name=’Bob’

Output:

The syntax for the TRUNCATE command:

TRUNCATE TABLE
Table_name;

Example:

select * from stu1

Output:

truncate table stu1

Output:

This deletes all the records from a table.