Mastering DML in SQL: How to Use INSERT, UPDATE, and DELETE to Handle Data

SQL (Structured Query Language) is a powerful language used to interact with databases. Among its core functionalities, Data Manipulation Language (DML) plays a crucial role in handling the actual data stored in tables. Whether you’re adding a new user to a database, changing a product price, or removing outdated entries, you’re using DML.

In this guide, we’ll explore three fundamental DML commands:

• INSERT – to add new records
• UPDATE – to modify existing records
• DELETE – to remove records

This tutorial is designed for absolute beginners and uses simple language and examples to help you learn effectively.

What is DML (Data Manipulation Language)?

DML is a subset of SQL used to manipulate the data stored in a database table. These operations affect the contents of the tables but do not alter the structure (which is handled by DDL – Data Definition Language).

Key operations in DML:

• Add new data (INSERT)
• Modify existing data (UPDATE)
• Remove data (DELETE)
• Read or query data (SELECT – often categorized under DQL)

DML commands interact directly with rows in tables and are usually part of transactional systems, meaning changes can be rolled back if needed.

1. INSERT – Adding New Records

The INSERT command allows you to add new rows (records) to a table.

Syntax:

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

Example:

Suppose we have a table called Customers:

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT
);

To insert a new customer:

INSERT INTO Customers (id, name, email, age)
VALUES (1, 'Alice Smith', 'alice@example.com', 28);

You can also insert multiple rows at once:

INSERT INTO Customers (id, name, email, age)
VALUES
(2, 'Bob Lee', 'bob@example.com', 35),
(3, 'Carol Jones', 'carol@example.com', 22);

💡 Tips:

• Make sure values match the column order.
• Not all columns are always required if default values exist.
• Unique constraints (like primary keys) must be respected.

2. UPDATE – Modifying Existing Records

The UPDATE command is used to change data in one or more rows of a table.

Syntax:

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

The WHERE clause is very important — without it, all rows will be updated!

Example:

Update Alice’s email:

UPDATE Customers
SET email = 'alice.new@example.com'
WHERE id = 1;

Update multiple columns:

UPDATE Customers
SET age = 29, name = 'Alice S.'
WHERE id = 1;

Update all records (⚠️ use with caution):

UPDATE Customers
SET age = age + 1;

This adds one year to every customer’s age.

💡 Tips:

• Always use WHERE to avoid accidental full-table updates.
• You can use conditions like AND, OR, and comparison operators.
• Test your WHERE clause with a SELECT before running an UPDATE.

3. DELETE – Removing Records

The DELETE command is used to remove rows from a table based on a condition.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

Delete the customer with ID 3:

DELETE FROM Customers
WHERE id = 3;

Delete all customers under age 25:

DELETE FROM Customers
WHERE age < 25;

Delete all rows from a table (⚠️ dangerous):

DELETE FROM Customers;

This will remove all records, but not the table itself.

💡 Tips:

• Like UPDATE, always double-check your WHERE clause.
• Use SELECT to preview which rows would be deleted.
• If supported, use BEGIN TRANSACTION and ROLLBACK for safety.

DML vs DDL: What’s the Difference?

Using DML with Transactions

Most SQL databases support transactions, allowing you to group DML commands together. If anything goes wrong, you can ROLLBACK to the previous state.

Example:

BEGIN;

UPDATE Customers
SET age = 40
WHERE id = 2;

DELETE FROM Customers
WHERE id = 5;

-- Oops, made a mistake!
ROLLBACK;

Or, to make changes permanent:

COMMIT;

Using transactions helps ensure data integrity during complex operations.

Common Mistakes Beginners Make with DML

1. ❌ Forgetting the WHERE clause in UPDATE or DELETE, which can alter or wipe out all rows.
2. ❌ Violating constraints, like inserting duplicate primary keys.
3. ❌ Using incorrect data types or column order in INSERT.
4. ❌ Not testing queries with SELECT before running them.
5. ❌ Not backing up data before running mass updates or deletions.

Real-Life Use Cases of DML

• 🛒 E-commerce: Add new product listings (INSERT), update prices (UPDATE), remove discontinued items (DELETE).
• 📱 Social Media: Insert new posts, edit content, delete old messages.
• 🏦 Banking: Add new customer data, update account info, delete inactive records.

No matter the industry, DML powers real-time data interactions behind the scenes.

Conclusion

DML commands—INSERT, UPDATE, and DELETE—are the lifeblood of SQL databases. They allow users to interact with and manage data efficiently. Understanding these commands is essential for developers, analysts, and anyone working with structured data.

With practice, you’ll gain the confidence to manipulate data safely and effectively. Just remember: always back up important data, test your queries, and use the WHERE clause wisely.