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

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

delete from emp where name=’JOHN’

or

Suppose we want to delete a department from dept table

delete from dept where dept_no= 10;

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

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

Delete from example;

It will delete all the rows from 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 and does not much less undo then delete statement

 

DELETE  rows from the table based on subquery

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’);

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);

Integrity Constraints error with Delete statement

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

Lets take an example