Using DELETE Statement in Oracle Database 23ai

The DELETE statement in Oracle Database 23ai is used to remove existing rows from a table. Unlike DROP TABLE, which removes the entire table, DELETE only takes away specific records while keeping the table structure intact. This tutorial explains how to use DELETE step by step with practical examples. You will also see how Oracle Database 23ai extends DELETE to handle modern features like JSON and vector data. By the end, you will know how to delete rows safely and effectively.

Understanding the DELETE Statement

The DELETE statement removes rows based on the condition you specify. If you do not use a WHERE clause, then all rows from the table will be deleted.

The basic structure is:

DELETE FROM table_name
WHERE condition;

COMMIT;

• DELETE FROM tells Oracle which table to remove rows from
• WHERE decides which rows to delete
• COMMIT makes the deletion permanent

Deleting a Single Row

To delete one record, you use a condition that matches a single row. For example, suppose you want to delete Michael from the employees table:

DELETE FROM employees
WHERE first_name = 'Michael';

COMMIT;

This removes Michael’s row but leaves all other employees untouched.

Deleting Multiple Rows

A condition can match more than one row. For example, if you want to delete all employees from department 20:

DELETE FROM employees
WHERE department_id = 20;

COMMIT;

This will remove all employees who belong to department 20 in one go.

Deleting All Rows

If you skip the WHERE clause, all rows will be deleted from the table. Use this carefully.

DELETE FROM employees;

COMMIT;

The table will remain, but it will be empty after this command.

Using DELETE with Conditions

You can apply logical operators like <, >, =, AND, OR, and IN in your DELETE statement. For example, to remove employees earning less than 6000:

DELETE FROM employees
WHERE salary < 6000;

COMMIT;

This removes only the employees who meet the condition.

Using Subqueries in DELETE

DELETE can use a subquery to decide which rows to remove. Suppose you want to delete employees who are in the same department as John:

DELETE FROM employees
WHERE department_id = (
   SELECT department_id
   FROM employees
   WHERE first_name = 'John'
);

COMMIT;

This removes everyone who shares John’s department.

Using DELETE with EXISTS

Sometimes you want to delete rows if related data exists in another table. For example, suppose you want to delete all departments that have no employees:

DELETE FROM departments d
WHERE NOT EXISTS (
   SELECT 1
   FROM employees e
   WHERE e.department_id = d.department_id
);

COMMIT;

This ensures only unused departments are removed.

Deleting JSON Data in Oracle 23ai

Oracle 23ai lets you modify or remove keys inside a JSON column using JSON_TRANSFORM. For example, to remove the ram field from a product’s JSON data:

UPDATE products
SET product_data = JSON_TRANSFORM(product_data,
                                  REMOVE '$.specs.ram')
WHERE product_id = 1;

COMMIT;

This does not delete the row but deletes a key from the JSON structure.

Deleting Vector Data in Oracle 23ai

In tables with vector embeddings, you can delete entire rows just like normal rows. For example, to delete a document with doc_id = 2:

DELETE FROM documents
WHERE doc_id = 2;

COMMIT;

This removes the row, including its vector data.

Best Practices for DELETE

1. Always use a WHERE clause unless you truly want to clear all rows.
2. Run a SELECT with the same condition first to verify which rows will be deleted.
3. Use transactions with COMMIT and ROLLBACK so you can undo mistakes.
4. For large deletes, consider deleting in smaller batches to avoid performance issues.
5. Remember that deleting rows is permanent once committed; always double-check conditions.

Conclusion

The DELETE statement in Oracle Database 23ai is a safe and powerful way to remove data that is no longer needed. From deleting a single row to removing multiple records at once, and even handling JSON and vector data, DELETE gives you flexibility while keeping control. By carefully using WHERE clauses, subqueries, and conditions, you can make sure your deletions are precise and reliable.

See also:

1. How to Insert Data into a Table in Oracle 23ai
2. Using UPDATE Statement in Oracle Database 23ai