Introduction

In Oracle Database, DML stands for Data Manipulation Language. DML commands allow you to insert, update, delete, and merge data stored inside your database tables.

Unlike DDL (Data Definition Language) — which changes the table structure — DML commands work only on the data itself.

Common DML Commands

Command Purpose
INSERT Add new records
UPDATE Modify existing records
DELETE Remove records from the table
MERGE Insert or update records (Upsert)

1. INSERT — Add New Records to Tables

The INSERT command is used to add new data into a table.

Example: Insert a Single Record

INSERT INTO SYSTEM.EMP_IND (empno, name, nickname, email)
VALUES (8, 'Sakthi', 'Sakthi', '[email protected]');

Explanation

This command inserts a new employee into the EMP_IND table.

Note. If empno is a primary key and the same value already exists, Oracle will raise an error:

ORA-00001: unique constraint violated

Example. Insert Multiple Records

INSERT ALL
  INTO SYSTEM.EMP_IND (empno, ename, nickname, email)
  VALUES (9, 'Ravi', 'Ravi', '[email protected]')
  INTO SYSTEM.EMP_IND (empno, ename, nickname, email)
  VALUES (10, 'Kumar', 'Kumar', '[email protected]')
SELECT * FROM dual;

Explanation

2. UPDATE — Modify Existing Records

The UPDATE command modifies existing data in a table.

Example. Update a Single Column

UPDATE SYSTEM.EMP_IND
SET ename = 'Sakthi Vel'
WHERE empno = 6;

Example. Update Multiple Columns

UPDATE SYSTEM.EMP_IND
SET ename = 'Sakthi V',
    email = '[email protected]'
WHERE empno = 6;

Tip. Always use a WHERE clause in UPDATE to avoid accidental updates to all rows.

3. DELETE — Remove Records From Tables

The DELETE command removes one or more rows from a table.

Example. Delete a Single Record

DELETE FROM SYSTEM.EMP_IND
WHERE empno = 3;

Example. Delete Multiple Records

DELETE FROM SYSTEM.EMP_IND
WHERE empno < 5 AND nickname = 'Ahmed.Samer';

Note. Deleting records does not reset sequences or primary keys. Only the data is removed.

4. MERGE — Upsert (Insert or Update)

The MERGE command combines INSERT and UPDATE operations. It either updates existing records or inserts new ones based on a matching condition.

Example. Merge Data (Upsert)

MERGE INTO SYSTEM.EMP_IND target
USING (SELECT 6 AS empno, 'Sakthi Vel' AS ename FROM dual) source
ON (target.empno = source.empno)
WHEN MATCHED THEN
  UPDATE SET target.ename = source.ename
WHEN NOT MATCHED THEN
  INSERT (empno, ename)
  VALUES (source.empno, source.ename);

Explanation

Important: COMMIT & ROLLBACK in DML

All DML operations in Oracle are transactional:

COMMIT; -- Save your work

ROLLBACK; -- Undo changes if needed

Always COMMIT after successful DML to avoid losing changes after disconnecting.

Summary Table: Oracle DML Commands

Command Purpose >COMMIT Required?
INSERT Add new records Yes
UPDATE Modify existing data Yes
DELETE Remove records Yes
MERGE Insert or Update data Yes

Conclusion

Mastering DML commands is essential for every Oracle Database developer. These operations allow you to: