Oracle SQL DML: INSERT, UPDATE and DELETE

Oracle SQL DML: INSERT, UPDATE and DELETE – Complete Guide

Data Manipulation Language (DML) lets you add, change, and remove rows in your tables. The three core DML statements — INSERT, UPDATE, and DELETE — are used in almost every Oracle application. Understanding their syntax, behavior, and transaction rules is essential for safe and efficient data management.

Learning Objectives
By the end of this guide you will know how to insert single and multiple rows, update existing data using subqueries, delete rows safely, understand the difference between DELETE and TRUNCATE, and control transactions with COMMIT, ROLLBACK, and SAVEPOINT.

1. INSERT — Adding Rows

The INSERT statement adds new rows to a table. You can specify columns explicitly or rely on the table’s column order.

-- Recommended: explicit column list
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Training', 1800);

-- Positional insert (values must match every column in table order)
INSERT INTO departments
VALUES (290, 'Consulting', NULL, 1900);

-- Inserting NULL explicitly or by omission
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (301, 'Singh', 'SINGH', SYSDATE, 'SA_REP');

Best practice: Always use an explicit column list. It makes your code clearer and protects it if the table structure changes later.

2. INSERT with a Subquery

You can insert multiple rows in one statement by using a SELECT instead of VALUES.

INSERT INTO emp_history (employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 50;

Important: When using a subquery, do not include the VALUES keyword. The SELECT replaces the entire VALUES clause.

3. UPDATE — Changing Existing Rows

UPDATE modifies one or more columns in existing rows. The WHERE clause determines which rows are affected.

-- Update a single row
UPDATE employees
SET    salary = 7000
WHERE  employee_id = 110;

-- Update multiple columns
UPDATE employees
SET    salary = salary * 1.1,
       commission_pct = 0.15
WHERE  department_id = 80;

-- Set a column to NULL
UPDATE employees
SET    manager_id = NULL
WHERE  employee_id = 100;

4. UPDATE with a Subquery

-- Update using a subquery
UPDATE employees
SET    salary = (SELECT AVG(salary)
                 FROM   employees
                 WHERE  department_id = 80)
WHERE  job_id = 'SA_REP';

Subquery rule: A subquery used in the SET clause must return exactly one row. Multiple rows cause an error.

5. DELETE — Removing Rows

-- Delete specific rows
DELETE FROM employees
WHERE employee_id = 300;

-- Delete all rows in a department
DELETE FROM employees
WHERE department_id = 50;

-- Delete every row (no WHERE clause)
DELETE FROM employees;

6. TRUNCATE vs DELETE

	DELETE (no WHERE)	TRUNCATE
Type	DML	DDL
Can be rolled back?	Yes	No (auto-commits)
WHERE clause supported?	Yes	No
Performance on large tables	Slower	Faster
Fires triggers?	Yes	No

7. Transaction Control

-- Make changes permanent
COMMIT;

-- Undo all changes since last COMMIT
ROLLBACK;

-- Partial rollback
SAVEPOINT sp1;
-- ... some statements ...
ROLLBACK TO sp1;

Important: Any DDL statement (CREATE, ALTER, DROP, TRUNCATE) automatically commits the current transaction before it runs.

8. Interactive Practice Quiz

Q1. What happens if you run an UPDATE statement without a WHERE clause?

A. Only the first row is updated B. Every row in the table is updated C. The statement fails with an error D. Only rows with NULL values are updated

Q2. You run INSERT, then CREATE TABLE, then ROLLBACK. How many of the INSERT rows survive?

A. None — ROLLBACK undoes everything B. All of them — CREATE TABLE commits the INSERT automatically C. Only the last INSERT D. The ROLLBACK fails because of the CREATE TABLE

Q3. Which statement permanently removes all rows from a table and cannot be rolled back?

A. DELETE FROM table_name; B. TRUNCATE TABLE table_name; C. DROP TABLE table_name; D. Both DELETE and TRUNCATE

Conclusion

INSERT, UPDATE, and DELETE are the fundamental statements for managing data in Oracle. Knowing when to use explicit column lists, how subqueries work in DML, the dangers of missing WHERE clauses, and the differences between DELETE and TRUNCATE will help you work safely and efficiently with your data.

Always consider transaction control — COMMIT and ROLLBACK give you the ability to confirm or undo your changes before they become permanent.