DELETE Statements

The DELETE statement is used to remove existing rows from a table. Similar to UPDATE, you can delete all rows, specific rows based on conditions, or use subqueries for more advanced deletions.

Deleting All Rows

This DELETE statement removes every single row from a table, effectively emptying it. This operation is permanent once committed.

Example 1: Oracle 19c SQL DELETE - All Rows

DELETE FROM old_audit_logs;
-- Deletes all records from the old_audit_logs table.

Explanation This statement removes every row from the old_audit_logs table. After execution, the table will be empty.

Deleting Specific Rows with WHERE Clause

This is the most common way to delete data, allowing you to remove only those rows that satisfy a specified condition. The WHERE clause is critical for preventing unintended data loss.

Example 2: Oracle 19c SQL DELETE - Specific Rows

DELETE FROM employees
WHERE department_id = 10;
-- Deletes all employees who belong to department ID 10.

Explanation This code deletes all rows from the employees table where the department_id is 10. Only employees in that specific department are removed.

Deleting with Subqueries

Subqueries can be used within a DELETE statement to identify the rows to be deleted based on a condition derived from another query. This allows for complex, conditional deletions.

Example 3: Oracle 19c SQL DELETE - With Subquery

DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE status = 'INACTIVE');
-- Deletes all orders placed by customers who are marked as 'INACTIVE'.

Explanation This DELETE statement removes orders belonging to customers whose status is 'INACTIVE'. The subquery identifies the customer_ids of inactive customers, and then those orders are deleted.