DELETE Query in DBMS

Deleting a single record

Where clause is used to provide a condition for deleting a particular record in the table

Consider the sample table EMP

Delete the record of employee number  7698

delete from emp
where empno=7698;

O/P

1 ROW DELETED

• A single employee present in the company with employee number 7698 will be deleted from the database table
• The previous table we have 7 rows but here we are having only 6 records i.e  one record has been deleted from the database table.

Deleting multiple records

Delete statement can be used to delete multiple rows at a time in a table

Delete the records of all employees whose salary is greater than 2500 expect the president

delete from emp 
where sal>2500  and job!=  'PRESIDENT';

O/P

4 ROWS DELETED

In the previous table, we have 7 records but after the execution of this query we are left with only three records i.e all those employees who are having a salary greater than 1000 except president will be deleted.

Deleting all records

• All the rows  present in the table will be deleted if there is no condition specified by using where clause  
• In this type of delete, the table will become empty and no records will be present to display

delete from emp;

O/P

7 ROWS DELETED

• All the seven rows present in the emp table  will be deleted and row-count becomes zero i.e  nothing to display.

DELETE V/S TRUNCATE

• Both these commands delete and truncate  make a table empty but there are certain differences where a super learner must be aware of

  TRUNCATE command usage

  truncate table emp;

• It removes all rows from a Table and makes a table empty
• This operation cannot be rolled back (table cannot be restored) and no triggers will be fired
• You cannot use a where clause and delete particular records you need to delete enter table data
• Truncate is faster and does not use a match under space as delete

DELETE command usage

delete from emp;

• Delete command is also used to remove rows from a table
• You can delete whether only particular records by using a condition with where a clause or entire rows can be deleted without using where class
• After performing delete operation the transactions can be rollbacked i.e that is a table can be restored after deleting 
• To make the changes permanent in a delete operation need to commit the transaction commit or rollback