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 and Truncate Query in SQL 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

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.

We have the perfect professional SQL Training Course for you!

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;

Delete Command

  • 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 certification.

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’;

Delete Command step 1

  • 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;

Delete Command step 2

  • 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;
Truncate Command
  • 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

Truncate Command 1

  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
1 + 10 =