DELETE Query in DBMS

DELETE Query

On this page we will discuss about DELETE Query in DBMS. DELETE statement is used to delete single or multiple records present in the existing database table based on a specific condition
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;

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

Delete the record of employee number  7698

delete from emp
where empno=7698;
O/P
1 ROW DELETED
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
  • 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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
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

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription