5.5 DML — INSERT, UPDATE, DELETE

Level: 5 | Version: v1.1 | Author: Meptrasoft

Overview

DML (Data Manipulation Language) is used to add, change, and remove data stored in database tables.

The three main commands are:

Command	Purpose
`INSERT`	Add new rows
`UPDATE`	Change existing rows
`DELETE`	Remove rows

DML works with the data inside a table, not the table structure itself.

DML — INSERT, UPDATE, DELETE: Modify Data inside tables — Figure 1: DML commands modify the data stored in a table—INSERT adds rows, UPDATE changes values, and DELETE removes rows.

Practice Table

This topic reuses the employees table from 5.2 DDL.

emp_id	emp_name	department	salary
1	Ravi Kumar	IT	45000
2	Anjali Mehta	HR	38000
3	Suresh Rao	Finance	42000
4	Priya Nair	IT	50000

INSERT

INSERT is used to add new rows to a table.

INSERT INTO employees
(emp_id, emp_name, department, salary)
VALUES
(5, 'Karthik Iyer', 'Finance', 40000);

A new employee is added to the table.

INSERT → Add data

UPDATE

UPDATE is used to change existing data.

UPDATE employees
SET salary = 55000
WHERE emp_id = 4;

Priya's salary changes from 50000 to 55000.

Important

Be careful with the WHERE condition.

UPDATE employees
SET salary = 55000;

Without WHERE, this updates the salary for every row.

UPDATE → Change existing data

DELETE

DELETE is used to remove rows from a table.

DELETE FROM employees
WHERE emp_id = 5;

This removes the employee whose ID is 5.

Important

Without a WHERE condition:

DELETE FROM employees;

all rows are targeted for deletion. The table structure remains.

DELETE → Remove rows

INSERT vs UPDATE vs DELETE

Command	What It Does	Table Structure
`INSERT`	Adds rows	Unchanged
`UPDATE`	Changes row values	Unchanged
`DELETE`	Removes rows	Unchanged

INSERT → ADD
UPDATE → CHANGE
DELETE → REMOVE

DML and Transactions

DML operations can be part of a transaction.

For example, to discard an uncommitted change:

BEGIN;

UPDATE employees
SET salary = 55000
WHERE emp_id = 4;

ROLLBACK;

The uncommitted change is undone.

Or, to permanently apply it:

BEGIN;

UPDATE employees
SET salary = 55000
WHERE emp_id = 4;

COMMIT;

The change is committed.

DML changes can be controlled through transactions, depending on the database system and transaction settings.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`INSERT` changes table structure	`INSERT` adds rows
`UPDATE` adds a new row	`UPDATE` changes existing row values
`DELETE` removes the table	`DELETE` removes rows
`UPDATE` without `WHERE` changes one row	It can change all rows
`DELETE` without `WHERE` removes one row	It can remove all rows
DML and DDL are the same	DML changes stored data; DDL manages structure
`ROLLBACK` always works for every DML statement in exactly the same way	Transaction behavior can vary by database system and configuration

Placement Quick Points

INSERT
→ ADD ROWS

UPDATE
→ CHANGE DATA

DELETE
→ REMOVE ROWS

DML stands for Data Manipulation Language.
INSERT adds new rows.
UPDATE changes existing values.
DELETE removes rows.
WHERE is important when you want to target specific rows.
DML operations can participate in transactions.

Interview Questions

What is DML?

DML stands for Data Manipulation Language and is used to add, modify, and remove data stored in tables.

What is INSERT used for?

INSERT adds new rows to a table.

What is UPDATE used for?

UPDATE changes values in existing rows.

What is DELETE used for?

DELETE removes rows from a table.

What happens if UPDATE is used without WHERE?

All rows that satisfy the statement's target are updated; for a simple table-wide update, that means every row.

What happens if DELETE is used without WHERE?

All rows in the targeted table are deleted.

Does DELETE remove the table?

No. DELETE removes rows while the table structure remains.

What is the difference between DML and DDL?

DML → Changes stored data
DDL → Manages database structure

Practice & Hands-On Exercises

Using the employees table:

Insert a new employee:

INSERT INTO employees (emp_id, emp_name, department, salary)
VALUES (5, 'Karthik Iyer', 'Finance', 40000);

Update an employee's salary:

UPDATE employees SET salary = 55000 WHERE emp_id = 4;

Update the department of one employee:

UPDATE employees SET department = 'Operations' WHERE emp_id = 2;

Delete one employee using emp_id:

DELETE FROM employees WHERE emp_id = 5;

Explain what happens if WHERE is removed from an UPDATE.
Explain what happens if WHERE is removed from a DELETE.
Write one INSERT, one UPDATE, and one DELETE statement.
Explain the difference between DML and DDL.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

INSERT → ADD
UPDATE → CHANGE
DELETE → REMOVE

DML commands modify the data inside tables: INSERT adds rows, UPDATE changes existing data, and DELETE removes rows.

End of DML — INSERT, UPDATE, DELETE