DELETE Statement is used to permanently removes existing records from a given table.
Most of the time QAs won’t have permission to perform this command on Development or Production environment. But they can do it on their won Testing environment.
By using DELETE command, we can delete one or more records in a table. WHERE clause is used with DELETE to remove only specific records. If not using WHERE clause with DELETE , it deletes all the records from table.
DELETE FROM <table_Name> WHERE [Condition];
We can combine N number of conditions using AND or OR operators in WHERE clause.
Lets take an example of Employee Table
ID | First_Name | Last_Name | City | Salary | Department
101 | Jenny | Wadra | Pune | 85800 | HR
102 | Mak | Patty | Mumbai | 18000 | IT
103 | John | Roy | Pune | 30800 | Admin
104 | Sammy | Desuza | Pune | 66000 | IT
105 | Rosy | Shenoy | Mumbai | 40000 | HR
106 | Rosy | Gomes | Pune | 42000 | Testing
Now let’s see how we can use DELETE command with some examples.
If you want to delete single record.
DELETE FROM Employee WHERE ID=102;
This query will delete a single employee from above table whose ID is 102.
You can also delete multiple records with providing conditions.
DELETE FROM Employee WHERE Salary BETWEEN 30000 AND 40000;
This query will delete multiple records from a Employee . ( ID 102 and 103 will get deleted)
You can also use < , > , <=, >= , AND , OR , IN , LIKE operators in Where condition to delete specific records
DELETE FROM Employee WHERE Salary>70000;
This query will delete all those records whose salary is greater than 70000. ( ID 101 will get deleted)
DELETE FROM Employee WHERE ID IN(104,105,106);
This query will DELETE employees from above table, with IDs 104,105,106.
DELETE FROM Employee WHERE City=’Mumbai’;
This query will DELETE employee from above table with IDs 104.
DELETE FROM Employee WHERE First_Name LIKE ’Ro%’;
This query will DELETE 2 employees from above table with First_Name as Rosy.
DELETE FROM Employee WHERE 1=1;
Output: All rows are deleted from the Employee. This is because the condition in the WHERE clause is true for all rows.
This query will delete all the records from employee table.
NOTE: Please note that the DELETE command cannot delete any rows of the table that would violate FOREIGN KEY or other constraints.
TRUNCATE command will delete entire table from database along with it’s records.
Being a QA is very rare you will use this command. As Testing team wont have permission to delete entire table, not even in Testing environment.
By using TRUNCATE Statement one can delete ALL the rows from a table and free the space containing the table
TRUNCATE TABLE table_name;
If we need to delete our sample table ‘Employee’
This will delete entire Employee table from database.
Difference between DELETE & TRUNCATE commands
|Delete command come under DML (Data Manipulation Language)||Truncate command come under DDL|
(Data Definition Language)
|By using DELETE command we can delete specific tuple.||By using TRUNCATE entire table will get deleted.|
|By using DELETE Statement we can delete rows from a table, without freeing the space containing the table|
|By using TRUNCATE Statement we can delete ALL the rows from a table and free the space containing the table(de allocating the data pages)|
DELETE FROM table_name [WHERE condition]; // where condition is optional
TRUNCATE TABLE table_name;
|In Delete statement, we can delete ALL records in table or we can delete specific rows.|
Ex: delete from employee; // entire table will get deleted
(where condition is optional, without where condition entire table will get deleted.)
Delete from employee where eid=1; // only 1 record will get deleted.
|Truncate statement is use to delete all the rows from table,|
Ex : TRUNCATE TABLE employee;
|In Delete statement, we can use where condition.||We can’t use where condition with it.|
|After performing a DELETE operation we can do COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.||TRUNCATE statement will remove all rows from a table; This operation cannot be rolled back.|
|Speed of execution is slow because it delete row by row data from table||Speed of execution is fast|
|By using DELETE command we are allowed to delete records that have foreign key constraints defined. (or we can say delete can be use with indexed view)||But this is not possible with TRUNCATE. We can’t execute TRUNCATE if foreign key constraints are defined. (or we can say truncate can’t be use with indexed view)|
|We can fire a trigger with DELETE statement because it deletes the data tuple wise from table. So with delete we are modifying the data after delete operation.||We can’t fire a trigger in TRUNCATE because the operation doesn’t delete individual tuple, it de allocates entire table or data page. So there will be no modification in the table.|
|If we are deleting a data using DELETE statement, we can recover that data.||But we need to use TRUNCATE very carefully because we can’t recover the data deleted using this statement.|