DELETE Statement - SQL Query
Different possible ways to delete StatementConsider the following table 'Students'
i) Delete a row where Stud_ID = 2.
ii) Delete all rows from the table.
iii) Delete table from database.
i) To delete row where Stud_ID = 2. The query should be written as:
DELETE FROM Students WHERE Stud_ID= 2
ii) To remove all rows, the query should be written as:
DELETE FROM Students
This query will delete only records present in the table and not table. Another command is used to remove all rows from the table:
TRUNCATE TABLE Students
If the column contains constraints or other dependencies, an error message will be displayed. This error is resolved by deleting the referenced constraints. The TRUNCATE Command is DDL(Data Definition Language) therefore TRUNCATE TABLE statement is much faster than DELETE FROM Statement.
iii) To delete table, the query should be written as:
DROP TABLE Students
This query will delete table 'students' permanently.
The delete command is used to delete a records from column.
DROP Table command is used to delete a whole table.
The DROP is DDL(Data Definition Language and The DELETE is DML (data Manipulation Language) therefore the DELETE command can be rolled back, while DROP command can not be rolled back.