Last updated on March 24th, 2019 at 01:45 pm
This SQL tutorial provides explanations, examples for Delete from table statement in Oracle
Delete from table statement is used in 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. we will looking at Oracle Sql syntax but this is valid for sql’s
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
Creating a Global Temporary Table in Oracle
Oracle Create table Syntax,Tip and Examples