Delete and Truncate commands in SQL
The Delete query in SQL only deletes records from the table, and it doesn’t make any changes in the definition, i.e., it only manipulates. Hence, it is DML (Data Manipulation Language). The Truncate command in SQL removes all rows from a table, i.e., it reinitializes the identity by making changes in the data definition. That’s the reason it is a DDL (Data Definition Language).
Let’s discuss in detail on:
Watch this Delete Query in SQL vs Truncate Command in SQL video
Delete Command in SQL
The Delete query in SQL is basically used to delete or remove one or more existing records from a table. It is used along with the Where clause. Though, the Where condition is optional in the Delete query.
How to Delete Table in SQL
Syntax
DELETE FROM table_name
[WHERE condition];
where DELETE, FROM, and WHERE are the keywords and table_name is the name of the table. And in condition section, a condition is given, followed by a semicolon.
How to Delete Row in SQL
Let’s use the Delete command in SQL and delete the record of an employee whose age is 33 years
DELETE FROM employee
WHERE e_age = 33;
- Please not that here only one record is affected
- After writing the query, click on the execute button to check for errors
- Once the query is executed, a message appears like ‘Commands completed successfully’
Wish to get certified in SQL! Learn SQL from top SQL experts and excel in your career with Intellipaat’s SQL online course and certification program.
How to Delete Column in SQL
Let’s delete a record with a condition where the name of the employee is sam
DELETE FROM employee
WHERE e_name = ‘sam’;
- Here also only one record is affected
- After writing the query, click on the execute button to check for errors
- Once the query is executed, a message appears like ‘Commands completed successfully’
- Let’s check the update
SELECT * from employee;
- After writing the query, click on the execute button to check for errors
- Once the query is executed, a message appears like ‘Commands completed successfully’
Truncate Command in SQL
The Truncate in SQL deletes all of the data inside a table. This command removes all records of the table but and retains the structure of the table
Come to Intellipaat’s SQL Community if you have more queries on SQL!
Syntax
TRUNCATE TABLE table_name;
where TRUNCATE TABLE is the keyword and table_name is the name of the table, followed by a semicolon.
- Let’s remove all the records of the employee table using the Truncate command in SQL
TRUNCATE TABLE employee;
- After writing the query, click on the execute button to check for errors
- Once the query is executed, a message appears like ‘Commands completed successfully’
- Let’s check if the structure of the employee table is retained or not
SELECT * FROM employee
- We can see that the structure is still intact
- After writing the query, click on the execute button to check for errors
- Once the query is executed, a message appears like ‘Commands completed successfully’
Prepare yourself for the industry by going through this Top SQL Interview Questions and Answers!
SQL Delete vs SQL Truncate
Well, this how we work with Delete query in SQL and where to use Truncate command in SQL. The table below gives a quick difference between these two commands.
This bring us to end of this part of tutorial where we learned how delete and truncate command in SQL. Here we learned how to delete column in SQL, how to delete row in SQL and how to delete table in SQL, and we learned how to use truncate table in SQL.