Introduction
Delete and truncate statements are used to manipulate and manage relational databases. Each has its features and usage. However, one might get confused between the two. Therefore it is essential to understand their differences to use them effectively.
In this article, we will discuss difference between delete and truncate statements by briefly discussing their concept, syntax, and properties. We will also look into some examples to understand the same by outlining some of the critical features and by presenting a tabular format to understand the comparison.
What is a DELETE statement?
The delete statement is an SQL command used to remove one or more rows from a table based on a specific condition. It is a DML (data manipulation command). This command allows us to remove unwanted or obsolete data from a database and can be used to delete single rows.
The DELETE statement is helpful for selective deletion. The DELETE statement removes specific rows based on specific conditions. It is slower than the TRUNCATE and DROP statements.
Syntax
DELETE FROM table_name WHERE condition;
In the above syntax, ‘table_name’ is the name of the table from which we want to delete rows, and ‘condition’ is the criteria or operational parameter, on the basis of which a row is deleted.
Example
DELETE FROM NinjaClass WHERE number<3;
The above SQL query will delete all the rows from the “NinjaClass” table where the number is less than 3.
The “NinjaClass” table has columns named “id”,”NinjaName” and “NinjaSkills”.Below is an example of what the output would look like
Before executing the DELETE statement:
Code
-- create a table
CREATE TABLE NinjaClass (
id INT PRIMARY KEY,
name varchar(30) NOT NULL,
position varchar(30) NOT NULL
);
-- insert some values
INSERT INTO NinjaClass VALUES (1, 'Ninja1', 'SDE1');
INSERT INTO NinjaClass VALUES (2, 'Ninja2', 'SDE2');
INSERT INTO NinjaClass VALUES (3, 'Ninja3', 'SDE2');
INSERT INTO NinjaClass VALUES (4, 'Ninja4', 'SDE1');
-- fetch some values
SELECT * FROM NinjaClass;
Output
After executing the DELETE statement
DELETE FROM NinjaClass WHERE number<3;
-- fetch some values
SELECT * FROM NinjaClass;
output
The delete statement permanently removes the data from the table. Once deleted, it cannot be undone. Therefore DELETE statement should be used with caution. We can also take a table backup before performing any delete operation on it.
568+ registered 
