Oracle

Data Manipulation Language (DML) in Oracle Database — With Practical Examples

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

  • INSERT ALL allows you to insert multiple records at once.
  • DUAL is a special Oracle table used for selecting constants.

 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

  • If empno 6 exists → updates the employee name.
  • If not → inserts a new record.

Important: COMMIT & ROLLBACK in DML

All DML operations in Oracle are transactional:

  • COMMIT → Saves the changes permanently.
  • ROLLBACK → Reverts changes made during the current transaction.

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:

  • Add, modify, or delete data.
  • Perform conditional inserts or updates with MERGE.
  • Maintain full control over transactions using COMMIT and ROLLBACK.
Membership not found