SQL: DELETE Query

What is Delete Query?

In SQL, the DELETE Query removes existing records from a table. Depending on the condition specified in the WHERE clause, we can delete a single record or multiple records.

NOTE: This command must be used with extreme caution because it permanently deletes the data.

Syntax:

DELETE FROM table_name [WHERE condition];  

where table_name is the table that has to be deleted, the WHERE clause in SQL DELETE statement is optional here.

Example:

Let’s take an example of student table:

ROLL	NAME	SEMESTER	YEAR
1	RAMESH	5	3
2	SUJIT	2	1
3	SURESH	1	1
4	SUJIT	7	4

• Delete all the records:

DELETE FROM student;

The given query will delete all the records from the student table, as we haven’t mentioned any conditions for deletion.

• Delete a particular Record from a Table:

DELETE FROM student WHERE NAME="SUJIT";

The above query will delete two records from the above table as there are two students with the name “SUJIT“.

Output:

ROLL	NAME	SEMESTER	YEAR
1	RAMESH	5	3
3	SURESH	1	1

TRUNCATE vs DELETE:

TRUNCATE and DELETE are both SQL commands for removing data from a table, but they have some key differences:

• DELETE is a DML (Data Manipulation Language) operation, whereas TRUNCATE is a DDL (Data Definition Language) operation. This means that TRUNCATE is used to modify the table’s structure, whereas DELETE is used to modify the data within the table.
• TRUNCATE deletes all rows from a table, whereas DELETE deletes specific rows based on a condition.
• TRUNCATE is not transactional, which means it cannot be reversed. DELETE, on the other hand, is a transactional command that can be reversed if necessary.
• DELETE does not reset the identity value to the initial seed value, whereas TRUNCATE does.

Note: also read about SQL: UPDATE Query

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py