DML Commands in SQL: A Complete Guide

DML stands for Data Manipulation Language, a category of SQL commands used to manage the data stored within database tables. It enables users to insert new records, update existing information, and delete unwanted data, making it an essential part of everyday database operations.

Whether you're managing customer records, processing transactions, updating inventory, or maintaining employee information, DML commands help keep database data accurate and up to date. Because of their role in handling real-world business data, they are among the most frequently used SQL commands.

In this blog, you will learn exactly what DML commands in SQL are, how each command works with real syntax and examples, where to use them, and what mistakes to avoid.

Transform your career with upGrad’s Data Science Course. Learn from industry experts, work on hands-on projects, and gain the skills top employer’s demand.

What Are DML Commands in SQL?

DML commands in SQL are a set of commands used to manipulate data stored inside database tables. They do not change the structure of the table itself. Instead, they work with the actual data sitting inside those tables.

Think of a database table like a spreadsheet. DML commands let you:

• Add new rows of data
• Read or fetch existing data
• Change existing data
• Remove unwanted data

There are four core DML commands in SQL:

DML vs DDL vs DCL: A Quick Comparison

Beginners often confuse DML with other SQL command types. Here is a simple breakdown:

Type	Full Form	Purpose	Example Commands
DML	Data Manipulation Language	Work with data	INSERT, SELECT, UPDATE, DELETE
DDL	Data Definition Language	Work with table structure	CREATE, ALTER, DROP
DCL	Data Control Language	Manage access and permissions	GRANT, REVOKE

DML is specifically about the data. DDL is about the table design. DCL is about who can do what.

Also Read: DQL Commands in SQL: A Complete Guide with Examples

INSERT Command: Adding New Data

The INSERT command puts new records into a table. It is the first thing you do when you want to start storing data.

Basic Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

DML Commands in SQL with Examples: INSERT

Say you have a table called students with columns student_id, name, and age.

Insert a single row:

INSERT INTO students (student_id, name, age)
VALUES (1, 'Riya Sharma', 22);

Insert multiple rows at once:

INSERT INTO students (student_id, name, age)
VALUES (2, 'Arjun Mehta', 24),
      (3, 'Priya Nair', 21),
      (4, 'Vikram Singh', 23);

Insert data from another table:

INSERT INTO students_archive
SELECT * FROM students
WHERE age > 25;

Key Points to Remember

• Column names and values must match in order.
• If a column has a NOT NULL constraint, you must provide a value.
• You can skip columns with default values defined in the table.
• Inserting data without specifying column names is possible but risky if the table structure changes.

Also Read: SQL INSERT INTO With Examples

SELECT Command: Reading Data from Tables

SELECT is the most frequently used of all DML commands in SQL. It lets you read data from one or more tables. You can fetch everything, or you can filter, sort, and group data as needed.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

DML Commands in SQL with Examples: SELECT

Fetch all data from a table:

SELECT * FROM students;

Fetch specific columns:

SELECT name, age FROM students;

Filter data using WHERE:

SELECT name FROM students
WHERE age > 21;

Sort results using ORDER BY:

SELECT name, age FROM students
ORDER BY age DESC;

Count records using aggregate functions:

SELECT COUNT(*) AS total_students FROM students;

Commonly Used SELECT Clauses

Clause	Purpose
WHERE	Filter rows based on condition
ORDER BY	Sort results (ASC or DESC)
GROUP BY	Group rows sharing a value
HAVING	Filter grouped results
LIMIT	Restrict number of rows returned
DISTINCT	Remove duplicate rows from output

Joins with SELECT

SELECT also powers table joins, which let you combine data from multiple tables.

SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;

SELECT is the backbone of data retrieval. Mastering it means mastering how to ask questions from your database.

UPDATE Command: Modifying Existing Data

The UPDATE command changes values in existing rows. It is one of the most powerful DML commands in SQL and needs to be used carefully.

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DML Commands in SQL with Examples: UPDATE

Update a single column:

UPDATE students
SET age = 25
WHERE student_id = 1;

Update multiple columns at once:

UPDATE students
SET name = 'Riya Sharma Kapoor', age = 26
WHERE student_id = 1;

Update using a condition on another column:

UPDATE students
SET age = age + 1
WHERE name = 'Arjun Mehta';

Warning: Always Use WHERE with UPDATE

Running UPDATE without a WHERE clause will update every single row in the table. That is almost never what you want.

-- This updates ALL rows. Very dangerous.
UPDATE students SET age = 0;

-- This is correct. Only updates one row.
UPDATE students SET age = 0 WHERE student_id = 3;

Always double-check your WHERE condition before running an UPDATE in a production database.

Also Read: What is Natural Join in SQL? Key Features, Implementation, and Best Practices

DELETE Command: Removing Data

The DELETE command removes rows from a table. Like UPDATE, it works with a WHERE clause to target specific rows.

Basic Syntax

DELETE FROM table_name
WHERE condition;

DML Commands in SQL with Examples: DELETE

Delete a specific row:

DELETE FROM students
WHERE student_id = 4;

Delete rows based on a condition:

DELETE FROM students
WHERE age < 18;

Delete all rows from a table (use carefully):

DELETE FROM students;

DELETE vs TRUNCATE: What Is the Difference?

This is a common confusion point. Here is a clear comparison:

Feature	DELETE	TRUNCATE
Command Type	DML	DDL
Can Use WHERE	Yes	No
Rollback Possible	Yes (within a transaction)	No (in most databases)
Speed	Slower for large data	Faster
Triggers Fired	Yes	No

Use DELETE when you want to remove specific rows. Use TRUNCATE only when you want to wipe all data quickly and you are certain about it.

Also Read: SQL Delete Statement Example

DML Commands in SQL: Transactions and ROLLBACK

One of the most useful features of DML commands is that they can be wrapped inside transactions. A transaction lets you group multiple DML operations and either save all of them together or undo all of them if something goes wrong.

DML Commands in SQL Syntax for Transactions

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 102;

COMMIT;

If something fails between the two UPDATE statements, you can roll back:

ROLLBACK;

This undoes every change made after the BEGIN TRANSACTION statement. Transactions are critical in banking, e-commerce, and any system where data accuracy matters.

ACID Properties and DML

DML commands follow ACID properties:

Property	Meaning
Atomicity	Either all changes happen or none do
Consistency	Data stays valid and correct
Isolation	Concurrent transactions do not interfere
Durability	Committed changes are permanent

Advanced DML Techniques You Should Know

Once you are comfortable with basic DML commands in SQL, these advanced techniques will take your skills further.

1. Subqueries Inside DML

You can use a SELECT query inside an INSERT, UPDATE, or DELETE statement.

UPDATE using a subquery:

UPDATE students
SET age = (SELECT AVG(age) FROM students)
WHERE student_id = 5;

DELETE using a subquery:

DELETE FROM students
WHERE student_id IN (
   SELECT student_id FROM enrollments WHERE course_id = 10
);

Also Read: SQL Server Architecture: Core Components, Data Management, and Practical Uses

2. Conditional UPDATE with CASE

UPDATE students
SET grade = CASE
   WHEN score >= 90 THEN 'A'
   WHEN score >= 75 THEN 'B'
   WHEN score >= 60 THEN 'C'
   ELSE 'F'
END;

3. MERGE Command (UPSERT)

Some databases like SQL Server and Oracle support MERGE, which combines INSERT and UPDATE into one statement. It inserts a row if it does not exist and updates it if it does.

MERGE INTO students AS target
USING new_students AS source
ON target.student_id = source.student_id
WHEN MATCHED THEN
   UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
   INSERT (student_id, name, age)
   VALUES (source.student_id, source.name, source.age);

Common Mistakes with DML Commands in SQL

Even experienced developers make these errors. Knowing them in advance will save you a lot of trouble.

• Forgetting the WHERE clause in UPDATE or DELETE. This affects every row in the table.
• Not using transactions when making multiple related changes. If one step fails, the other may still commit.
• Wrong data types in INSERT or UPDATE. Inserting a string into a number column throws an error.
• Selecting all columns with SELECT* in large tables. This pulls unnecessary data and slows performance.
• Not testing on sample data first before running DELETE or UPDATE in a live database.

Conclusion

DML commands in SQL are the foundation of working with data. INSERT, SELECT, UPDATE, and DELETE give you full control over what goes into your database, what you read from it, and what gets changed or removed. 

Start with the basics: write simple SELECT queries, practice INSERT with small datasets, and always use WHERE with UPDATE and DELETE. Once that feels comfortable, move on to transactions, subqueries, and advanced patterns like MERGE.

If you want to go deeper into SQL and build real database skills, upGrad offers structured Data Science Courses to take you from beginner to job-ready.

Want personalized guidance on SQL and Data Science and upskilling? Speak with an expert for a free 1:1 counselling session today.