DELETE Statement in Oracle SQL – PLSQL

The DELETE statement in Oracle SQL / PLSQL allows us to delete record(s) in a table.

Syntax for the DELETE statement in Oracle SQL / PLSQL is:

DELETE FROM table_name
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Suppose we want to delete the employee having employee_id = 105 from the ‘employee’ table.
The same can be achieved using the DELETE statement as follows

[sourcecode language=”sql”]
DELETE FROM employee
WHERE employee_id = 105;
[/sourcecode]

The above statement will delete record from the table where employee_id = 105

Now if we query the ‘employee’ table as;

[sourcecode language=”sql”]
SELECT *
FROM employee;
[/sourcecode]

We will get the following result:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support

Here we can see that a record with employee_id = 105 has been successfully deleted from the ‘employee’ table.


Scenario 2:

Deleting more than one record at a time in an already existing table:
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can see that we have 2 employees in the ‘Sales’ department and we want to delete them.
We can achieve the same as:

[sourcecode language=”sql”]
DELETE FROM employee
WHERE department = ‘Sales’;
[/sourcecode]

Once we have run the above code we can query the ‘employee’ table and the following will be the result:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support

Here we have successfully deleted the desired records from ‘employee’ table in one shot.


Leave a Reply

Your email address will not be published. Required fields are marked *