Deleting Data

The DELETE statement removes rows from a table. Like UPDATE, it requires careful use of the WHERE clause — omitting it removes every row in the table. Oracle also provides TRUNCATE for fast full-table removal with important differences in behaviour, and DELETE CASCADE for handling child rows when foreign key constraints are in place. This lesson covers all three approaches, how to verify deletions before committing, and how to recover from accidental deletes using ROLLBACK and Oracle Flashback.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Basic DELETE

A DELETE statement removes all rows that satisfy the WHERE condition. Without a WHERE clause, every row in the table is deleted — Oracle does not prompt for confirmation. The same discipline applied to UPDATE applies here: always run a SELECT with the same WHERE clause first to confirm exactly which rows will be removed.

DELETE is a transactional operation — the rows are removed in the current session but remain visible to other sessions and can be recovered with ROLLBACK until COMMIT is issued. Undo segments hold the deleted row data for the duration of the transaction.

-- Always confirm affected rows with SELECT before deleting
SELECT order_id, customer_id, status, order_date
FROM   orders
WHERE  status = 'cancelled'
AND    order_date < DATE '2024-01-01';

-- Delete cancelled orders older than a specific date
DELETE FROM orders
WHERE  status     = 'cancelled'
AND    order_date < DATE '2024-01-01';

-- Verify the deletion — still in transaction, can ROLLBACK
SELECT COUNT(*) AS remaining_cancelled
FROM   orders
WHERE  status = 'cancelled';

-- Delete a single row by primary key — the safest form of DELETE
DELETE FROM customers
WHERE  customer_id = 15;

-- Delete using IN
DELETE FROM products
WHERE  product_id IN (20, 21, 22);

-- Delete all inactive products with zero stock
DELETE FROM products
WHERE  active_flag = 'N'
AND    stock_qty   = 0;

COMMIT;

• Always run a SELECT with the same WHERE clause before a DELETE — confirm the row count and inspect the rows before removing them permanently after COMMIT
• Deleting by primary key — WHERE customer_id = 15 — is the safest form because it identifies exactly one row with certainty; deleting by non-unique column values can affect more rows than expected
• DELETE without a WHERE clause deletes every row in the table but leaves the table structure, indexes, and constraints intact — this is the key difference from DROP TABLE

DELETE with Foreign Key Constraints

When a table has child rows in another table linked by a foreign key, deleting the parent row raises ORA-02292: integrity constraint violated — child record found. Oracle protects referential integrity and will not leave orphaned child rows. There are three ways to handle this: delete child rows first, use ON DELETE CASCADE on the foreign key definition, or use ON DELETE SET NULL.

-- Attempting to delete a customer who has orders raises ORA-02292
DELETE FROM customers
WHERE  customer_id = 1;
-- ORA-02292: integrity constraint (DATAPLEXA.FK_ORDERS_CUSTOMERS) violated - child record found

-- Correct approach 1 — delete child rows first, then the parent
DELETE FROM payments
WHERE  order_id IN (SELECT order_id FROM orders WHERE customer_id = 1);

DELETE FROM order_items
WHERE  order_id IN (SELECT order_id FROM orders WHERE customer_id = 1);

DELETE FROM orders
WHERE  customer_id = 1;

DELETE FROM customers
WHERE  customer_id = 1;

ROLLBACK;  -- undo all four deletes — they are in the same transaction

-- Correct approach 2 — ON DELETE CASCADE defined on the foreign key
-- When the parent is deleted, Oracle automatically deletes all matching child rows
-- This requires the FK to be defined with ON DELETE CASCADE at table creation time:
-- CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
--     REFERENCES customers(customer_id) ON DELETE CASCADE

-- Correct approach 3 — ON DELETE SET NULL
-- Child rows are kept but their FK column is set to NULL instead of being deleted
-- CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
--     REFERENCES customers(customer_id) ON DELETE SET NULL

-- Check which foreign keys have CASCADE or SET NULL behaviour
SELECT constraint_name,
       table_name,
       r_constraint_name,
       delete_rule
FROM   user_constraints
WHERE  constraint_type = 'R'
ORDER BY table_name;

• The delete_rule column in user_constraints shows NO ACTION, CASCADE, or SET NULL for each foreign key — query it to confirm cascade behaviour before deleting parent rows
• ON DELETE CASCADE is convenient but dangerous on deep dependency chains — deleting one parent row can silently cascade through many levels of child tables removing far more data than intended; always audit the cascade path before using it
• The manual child-first delete order must follow the dependency chain from the deepest child up — in the DataplexaStore schema: payments and order_items first, then orders, then customers

DELETE with a Subquery

A subquery in the WHERE clause identifies rows to delete based on conditions in another table. This is the cleanest way to remove rows when the deletion criteria live in a related table rather than in the target table itself.

-- Delete order items belonging to cancelled orders
DELETE FROM order_items
WHERE  order_id IN (SELECT order_id
                    FROM   orders
                    WHERE  status = 'cancelled');

-- Delete payments for orders placed by customers in a specific country
DELETE FROM payments
WHERE  order_id IN (SELECT o.order_id
                    FROM   orders    o
                    JOIN   customers c ON c.customer_id = o.customer_id
                    WHERE  c.country  = 'Test Country');

-- Delete employees who left before 2020 and have no payroll history
DELETE FROM employees
WHERE  hire_date  < DATE '2020-01-01'
AND    employee_id NOT IN (SELECT DISTINCT employee_id
                           FROM   payroll_history
                           WHERE  employee_id IS NOT NULL);

-- Correlated DELETE — remove duplicate salary rows keeping only the most recent
DELETE FROM salaries s1
WHERE  salary_id <> (SELECT MAX(s2.salary_id)
                     FROM   salaries s2
                     WHERE  s2.employee_id = s1.employee_id
                     AND    s2.salary_type  = s1.salary_type);

ROLLBACK;

• The correlated delete pattern — where the subquery references the outer table — is the standard technique for removing duplicate rows while keeping one representative row per group
• As with NOT IN in a WHERE clause, a NOT IN subquery used in a DELETE must exclude NULL values from the subquery result — add WHERE column IS NOT NULL inside the subquery to avoid the NULL trap returning zero deletions
• Zero rows deleted is not an error — Oracle succeeds silently when the WHERE condition matches nothing; always verify the row count after a bulk delete

TRUNCATE vs DELETE

TRUNCATE TABLE removes all rows from a table instantly. It is a DDL statement — not DML — which means it commits immediately and cannot be rolled back. It bypasses undo segment logging and is orders of magnitude faster than DELETE FROM table without a WHERE clause on large tables because it deallocates the data blocks directly rather than logging each row deletion individually.

The tradeoff is loss of control: there is no WHERE clause, no row-level logging, and no rollback. Use TRUNCATE only when you are certain every row in the table must be removed and recovery is not needed.

-- DELETE without WHERE — removes all rows, can be rolled back
DELETE FROM products_archive;
ROLLBACK;  -- rows are restored

-- TRUNCATE — removes all rows instantly, auto-commits, cannot be rolled back
TRUNCATE TABLE products_archive;
-- ROLLBACK here does nothing — the TRUNCATE has already committed

-- TRUNCATE resets the high-water mark — storage is returned to the tablespace
-- DELETE does not reset the high-water mark — empty blocks remain allocated

-- TRUNCATE is blocked if a foreign key from another table references this table
-- Even if the child table is empty, the FK constraint prevents TRUNCATE
-- You must disable or drop the FK first:
ALTER TABLE order_items DISABLE CONSTRAINT fk_order_items_orders;
TRUNCATE TABLE orders;
ALTER TABLE order_items ENABLE CONSTRAINT fk_order_items_orders;

-- Confirm the table is empty after TRUNCATE
SELECT COUNT(*) AS row_count FROM products_archive;

• TRUNCATE is a DDL statement — it issues an implicit COMMIT before it executes, committing any open DML in the current transaction before the table is cleared; this is a common gotcha when TRUNCATE is mixed with pending inserts or updates
• TRUNCATE resets the table's high-water mark, returning storage blocks to the tablespace — DELETE leaves empty blocks allocated, which is why TRUNCATE followed by a bulk insert is faster than delete-and-reinsert for staging tables
• An enabled foreign key from any child table blocks TRUNCATE on the parent — even if the child table contains no rows; disable or drop the FK constraint first, then re-enable it after truncating

ROLLBACK and Flashback Recovery

Because DELETE is transactional, uncommitted deletions can always be reversed with ROLLBACK. For rows deleted and committed accidentally, Oracle Flashback Query allows reading the table as it appeared at a past point in time using the AS OF TIMESTAMP or AS OF SCN clause, as long as the undo data has not yet been overwritten.

-- ROLLBACK undoes all uncommitted DELETE statements in the current transaction
DELETE FROM customers WHERE customer_id = 3;
DELETE FROM customers WHERE customer_id = 4;
ROLLBACK;  -- both deletes are undone — rows are fully restored

-- SAVEPOINT allows partial rollback within a transaction
SAVEPOINT before_product_delete;

DELETE FROM products WHERE active_flag = 'N';

-- Something went wrong — roll back only to the savepoint, not the whole transaction
ROLLBACK TO SAVEPOINT before_product_delete;

-- Flashback Query — read the table as it was 15 minutes ago
-- Useful for recovering accidentally committed deletes
SELECT *
FROM   customers AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE  customer_id = 3;

-- Recover accidentally committed rows using Flashback Query with INSERT INTO SELECT
INSERT INTO customers
SELECT *
FROM   customers AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE  customer_id NOT IN (SELECT customer_id FROM customers);

COMMIT;

• SAVEPOINT marks a point within a transaction that can be rolled back to without undoing the entire transaction — useful in long scripts where only one section needs to be retried
• Flashback Query depends on undo retention — if the undo tablespace has been overwritten since the commit, Oracle raises ORA-01555: snapshot too old; the default undo retention period is typically 15 minutes to a few hours depending on database configuration
• The recovery pattern — INSERT INTO ... SELECT * FROM table AS OF TIMESTAMP ... WHERE pk NOT IN (SELECT pk FROM table) — safely re-inserts only rows that no longer exist in the live table, avoiding primary key violations

Summary

Practice Questions

Quiz

Next up — Aggregate Functions — Learn how to summarise data using COUNT, SUM, AVG, MIN, and MAX, and how GROUP BY and HAVING work with aggregates across the DataplexaStore and DataplexaHR schemas.