How to delete a row in oracle

This SQL tutorial provides explanations, examples for Delete from the 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 delete syntax in Oracle

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

Important points.

• Delete statement requires a lock on the table
• We need to do commit to permanently  change the date  (commit;)
• We can use the 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 the delete statement, then you can first fire the select statement with the same where clause to find out the number of rows

Let’s take a few ways to use the DELETE statement

DELETE specific rows from the table or How do I delete a record 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 the dept table

delete from dept where dept_no= 10;
commit;

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

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

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

Suppose we want to delete all rows from the 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 the oracle table and print how many rows are deleted which is actually the count of the table.

This does not reset the High watermark of the table. and it uses undo. You might want to use Truncate table in Oracle command which reset the HWM(High watermark) and does much less undo than delete statement

DELETE  rows from the table based on subquery in Oracle

Delete statement can be used with a 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 feeds 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 deleting using subquery only. This is used when you want to compare two tables to delete the data

Suppose you want to delete records from the 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;

I hope you like this content on How to delete a row in oracle

Related Articles
Alter Table in Oracle : Alter table in oracle is used to modify a column, drop and add constraints, change the datatype of the table column, change the table storage parameters
Creating a Global Temporary Table in Oracle
Update statement in oracle : We use the Update statement in oracle to modify the existing rows in the oracle table in the oracle database. An update can be executed in multiple ways
DROP TABLE ORACLE: Learn about drop table in Oracle, Drop table if exists in Oracle, drop multiple tables in one command, drop table cascade constraints
Oracle Create table: Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
Oracle documentation on delete
oracle delete cascade: Check this post on how to resolve ORA-02292 using Oracle delete cascade with detailed example and explanation
Rownum in Oracle