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 conditionIn 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 |
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
Delete keyword in C++
Delete Operator in C++
Delete keyword in C++ is very helpful while dynamically allocate memory. Dynamic memory allocation enables a programmer to create and delete memory as per the requirement at any point of time other result memory wastage can be reduced. The 2 keywordsnew
and delete
are used for allocation and deallocation of memory respectively. Delete keyword in C++
Already learned about the new keyword in detail now will see how delete keyword works
Delete: Delete keyword in C++ is used to erase the memory allocated dynamically memory that is created using the new keyword and give back to the heap free store
- Memory allocated dynamically using a new keyword is not deleted automatically user explicitly needs to delete it using the delete operator
- Delete Keyword cannot delete memories that are created statically(compiler allocated memory in the stack)
Syntax:
delete ;
Example
delete p; //delete individual object delete []p;// delete entire array
C++ program demonstrates the usage of delete keyword
#include <iostream> using namespace std; int main() { // Allocate memory for an array of integers int* arr = new int[5]; // Assign values to the array for(int i = 0; i < 5; i++) { arr[i] = i; } // Print the values of the array for(int i = 0; i < 5; i++) { cout << arr[i] << " "; } cout << endl; // Deallocate the memory delete[] arr; for(int i=0; i<5; i++){ cout << arr[i] << " "; } return 0; }
Output
0 1 2 3 4 1533846632 5 -1838056283 -1071475926 4
How delete works?
The new operator takes memory from freestore and allocates in the program whenever we delete the memory using delete keyword it will return the memory of the object allocated to the free store so that the free store can use it for another new allocation requestDifference between malloc()/free() and new/delete
- both malloc()/free and new/delete is used for dynamic memory management
- The new and delete operators are having simple Syntax compare with these functions, and functions require to perform explicit Type casting whereas new/delete implicitly typecast based upon the data
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
There has been a critical error on this website.
Login/Signup to comment