DELETE

1.    DELETE is used to remove data from a table.

2.    It removes rows, not the table itself.

3.    You can choose which rows to delete by giving a condition.

4.    If you don’t give a condition, all rows will be deleted.

5.    It is part of commands used to change data in a database.

6.    You need permission to delete data from a table.

7.    After deleting, the data is not gone until you save the change.

8.    You can cancel the delete if you haven’t saved it yet.

9.    Some extra actions may happen automatically when you delete (like triggers).

10. Deleting a lot of data can make the process slower.

--Delete rows with a specific condition

DELETE FROM employees WHERE department_id = 10;

 

--Delete all rows in a table

DELETE FROM employees;

 

--Delete with AND condition

DELETE FROM employees WHERE department_id = 20 AND job_id = 'SA_REP';

 

--Delete with OR condition

DELETE FROM employees WHERE department_id = 30 OR department_id = 40;

 

--Delete using IN clause

DELETE FROM employees WHERE department_id IN (10, 20, 30);

 

--Delete using NOT IN clause

DELETE FROM employees WHERE department_id NOT IN (50, 60);

 

--Delete using EXISTS

DELETE FROM employees e

WHERE EXISTS (

    SELECT 1 FROM departments d

    WHERE d.manager_id = e.employee_id

);

 

--Delete using subquery

DELETE FROM employees

WHERE department_id = (

    SELECT department_id FROM departments WHERE department_name = 'Sales'

);

 

--Delete using EXISTS as a workaround for JOIN

DELETE FROM employees e

WHERE EXISTS (

    SELECT 1 FROM departments d

    WHERE d.department_id = e.department_id AND d.location_id = 1700

);

 

--Delete top N rows using ROWNUM

DELETE FROM employees

WHERE ROWNUM <= 5;

 

--Delete rows older than a specific date

DELETE FROM orders

WHERE order_date < TO_DATE('2023-01-01', 'YYYY-MM-DD');

 

--Delete rows using LIKE

DELETE FROM customers

WHERE customer_name LIKE 'Test%';

 

--Delete using BETWEEN

DELETE FROM orders

WHERE order_id BETWEEN 100 AND 200;

 

--Delete rows with NULL values

DELETE FROM employees

WHERE commission_pct IS NULL;

 

--Delete rows where column IS NOT NULL

DELETE FROM employees

WHERE commission_pct IS NOT NULL;

 

--Delete using IN subquery from another table

DELETE FROM employees

WHERE department_id IN (

    SELECT department_id FROM departments WHERE location_id = 1000

);

 

--Delete duplicate rows using ROWID

DELETE FROM employees e

WHERE ROWID > (

    SELECT MIN(ROWID)

    FROM employees

    WHERE employee_id = e.employee_id

);

 

--Delete with RETURNING clause

DELETE FROM employees

WHERE employee_id = 101

RETURNING employee_id, last_name INTO :emp_id, :emp_name;

 

--Delete with explicit COMMIT

DELETE FROM employees WHERE department_id = 90;

COMMIT;

 

--Delete with ROLLBACK (for testing transactions)

DELETE FROM employees WHERE employee_id = 120;

ROLLBACK;