DELETE Query in DBMS

DELETE  Query in DBMS

 

In this article, we will learn about DELETE Query in DBMS.

In  Real world scenario if the user wants to delete his Facebook account the user just click the delete option but at the backend, DELETE command is executed and the record with particular details is been deleted

DELETE statement is used to delete  single or multiple records present in the existing database table based on a specific condition

Basic Syntax for the DELETE  statement

DELETE FROM table_name
WHERE condition;

 

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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020

Delete the record of employee number  7698

delete from emp
where empno=7698;

O/P

1 ROW DELETED
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
  • 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
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

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