Delete from table in Oracle

This SQL tutorial provides explanations, examples for Delete from table in Oracle

Delete from table  is used in Oracle Sql to delete the rows in the table. It is a DML (Data Manipulation language) statement. It is used to delete the subset of existing rows or all existing rows from the table. 

Here is the syntax

Here table  is the table name and condition specifies the rows to be deleted   and this is composed of columns,subqueries and expressions

Important points.

• Delete statement requires lock on the table
• We need to do commit to permanently  change the date  (commit;)
• We can use use rollback command to undo the delete statement changes ( rollback;)
• if on delete Triggers are defined ,it will be fired for each row deleted
• if you want to find out how many rows will be deleted from delete statement,then you can first fire the select statement with the same where clause to find out the number of rows

Lets take few ways to use DELETE statement

DELETE specific rows from the table or How do I delete a row from Oracle

Suppose we want to delete the rows where column name name=’JOHN’

delete from emp where name='JOHN';
commit;

or 
delete from emp where name='JOHN';
rollback;

or

Suppose we want to delete a department from dept table

delete from dept where dept_no= 10;
commit;

These statement will delete all the rows containing that particular column value. After delete statement is completed,  it will shows how many rows are deleted.  we need to do commit to permanently make the changes.

If no rows are deleted , it will shows 0 rows deleted.

DELETE ALL rows from the table in Oracle or How do I remove all records from the table

Suppose we want to delete all rows from table example,then the statement would be

Delete from example;

delete from employee;

delete from fnd_users;
commit;

It will delete all the rows from oracle table  and print how many rows are deleted which is actually the count of the table.

This does not reset the High water mark of the table. and it uses undo. You might want to use Truncate table example command which reset the HWM(High water mark) and does not much less undo then delete statement

DELETE  rows from the table based on subquery in Oracle

Delete statement can be used with subquery to delete the rows

Suppose we want to delete the rows in Emp based on dept name in dept table, then delete statement would be like

delete from emp where dept_id = (select dept_id from dept where dept_name='SALES');
commit;

The above statement would first look for dept_id from the dept table where dept_name is sales. and then it feed the dept_id to the delete statement for the emp table

DELETE  rows from the table using EXISTS/NOT EXISTS clause

This is another form of delete using subquery only. This is used when you want to compare two table to delete the data

Suppose you want to delete records from dept table where there is no employee

delete from
FROM dept d
WHERE NOT EXISTS
(SELECT * FROM emp e
WHERE d.dept_id
= e.dept_id);
commit;

Integrity Constraints error with Delete statement

If you are deleting rows from table which is referenced by foreign key of the another table , then you will get integrity constraints errors

Lets take an example

CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
);

CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)

SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>

SQL> desc dept
Name Null? Type
----------------------------------------- -------- -----------------------

DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL>

insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');

insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );

insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
commit;

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10

7782 CLARK MANAGER 7839 09-JUN-08 2450 10

7788 SCOTT ANALYST 7566 09-JUN-12 3000 20

7789 TPM ANALYST 7566 09-JUN-17 3000

7790 TOM ANALYST 7567 09-JUL-17 4000

4534 xyz 1000 20

4576 abc 1000

7560 T1OM ANALYST 7567 09-JUL-17 4000 20

SQL> delete from dept;
delete from dept
*
ERROR at line 1:
ORA-02292: integrity constraints FK_DEPTNO violated -child record found

This can be avoided if we specify on delete cascade option while creating the EMP table. Then  when all the rows of the dept are deleted, all the records from EMP are also deleted

CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
ON DELETE CASCADE
);

CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>

SQL> desc dept
Name Null? Type
----------------------------------------- -------- ---------------

DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL>

insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');

insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );

insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
commit;
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10

7782 CLARK MANAGER 7839 09-JUN-08 2450 10

7788 SCOTT ANALYST 7566 09-JUN-12 3000 20

7789 TPM ANALYST 7566 09-JUN-17 3000

7790 TOM ANALYST 7567 09-JUL-17 4000

4534 xyz 1000 20

4576 abc 1000

7560 T1OM ANALYST 7567 09-JUL-17 4000 20

SQL> delete from dept;

4 rows deleted

SQL> commit;

SQL> Select count(*) from emp

0 rows

So all the records from emp are also deleted

Related Articles

How to Alter Table in Oracle
Creating a Global Temporary Table in Oracle
Update statement in oracle
DROP TABLE ORACLE statement
Oracle Create table Syntax,Tip and Examples
Oracle documentation on delete