SQL Delete Statement

Delete Statement 

Delete Statement :-Delete statement is used for delete records from table . Delete statement is a DML (Data Manipulation Language) statement that's why  Rollback is possible for delete statement.

Syntax :-  Below is the syntax for delete statement

DELETE from <Table_name> WHERE <condition>;

Note:- If "where <condition>" is not used in statement then delete statement will delete all records from table

Example1:- Delete all records from table EMP1
SQL>DELETE from EMP1;

Example2 :- Delete records of department 10

 SQL>DELETE from EMP1 where deptno=10;

Note:- Before going to delete, you have to verify records by select command which need to be delete select * from student where id<104;


Delete duplicate records :- For deleting duplicate records from table, first need to decide duplication on basis of column/columns.

Here is an example




in above table if we have decide "EMPNO" and "ENAME" columns for duplication then all three records are duplicate.


if we decide "EMPNO","ENAME" and "JOB" for duplication the first 2 records are duplicate.

if we decided all four columns for duplication then no record is duplicate .

Here is query to delete duplicate records on basis of first 3 columns

SQL>delete from emp where rowid not in (select max(rowid) from emp group by empno,ename,job);
 

Delete records on basis of Sub-Query :- 

SQL>delete from emp where deptno=(select deptno from dept where loc='LONDON');