PL/SQL Delete

Oracle PL/SQL provides a mechanism for deleting data from database tables. The DELETE statement is used to delete one or more rows from a table.

The DELETE statement is part of Data Manipulation Language and allows the user to delete a single record or multiple records in a table. The syntax of the DELETE statement is as follows:

Syntax:

DELETE FROM table; -- delete all records
DELETE FROM table WHERE condition; -- delete single or multiple records;

The WHERE clause is used to specify the conditions that determine which rows will be deleted. If the WHERE clause is omitted, all rows in the table will be deleted.

It is also possible to delete multiple rows from a table using a single DELETE statement. This can be done by specifying multiple conditions in the WHERE clause, separated by the OR operator. For example, to delete all rows from a table where the value of the column X is equal to 1 or 2, the following statement can be used:

DELETE FROM  WHERE X=1 OR X=2;

It is also possible to delete all rows from a table using a subquery. A subquery is a query that is nested within another query. The following statement will delete all rows from the table where the value of the column X is equal to the value of the column Y in another table:

DELETE FROM  WHERE X=(SELECT Y FROM );

Oracle PL/SQL also provides a mechanism for deleting data from tables using variables. This can be done by specifying the name of the variable in the WHERE clause, as follows:

DELETE FROM  WHERE column_name=:myvariable;

Oracle PL/SQL also provides a mechanism for deleting data from tables using foreign keys. This can be done by specifying the name of the foreign key in the WHERE clause, as follows:

DELETE FROM  WHERE =;

Oracle PL/SQL also provides a mechanism for deleting data from tables using cascade delete. This can be done by specifying the CASCADE keyword in the DELETE statement, as follows:

DELETE FROM  WHERE  CASCADE;

Cascade delete will delete all rows from the table that match the condition, as well as all rows from any child tables that have a foreign key pointing to the table being deleted.

Oracle PL/SQL also provides a mechanism for deleting data from tables using truncate. This can be done by specifying the TRUNCATE keyword in the DELETE statement, as follows:

DELETE FROM  TRUNCATE;

Truncate will delete all rows from the table, but will not delete the table itself. Truncate is faster than delete because it does not generate undo information.

Example 1:

DELETE FROM employees; -- delete all records
DELETE FROM employees WHERE name='Olivia' and id=7900 ; -- delete single record;
DELETE FROM employees WHERE dept_id=10; -- delete single or multiple records;

Example 2:

DELETE FROM employees e
 WHERE e.city_id = (SELECT c.city_id FROM cities c WHERE c.city_name='London')
 AND EXISTS (SELECT d.dept_id FROM departments d WHERE d.dept_id=e.dept_id and d.dept_id=10);