Introduction SQL is not just about querying data—it also includes powerful commands to modify data within tables. These Data Manipulation Language (DML) commands, such as INSERT, UPDATE, and DELETE, enable you to add, modify, or remove rows in a database. In this article, we’ll explore these commands with practical examples. Data Manipulation Language (DML) Sample Table: Employees Let’s start with a sample Employees table to demonstrate the examples: EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

70000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

72000

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

70000

IT EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

70000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

72000

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

70000

IT EmployeeID

Name

Position

Salary

Department EmployeeID EmployeeID Name Name Position Position Salary Salary Department Department 1

Alice

Developer

70000

IT 1 1 Alice Alice Developer Developer 70000 70000 IT IT 2

Bob

Designer

65000

Design 2 2 Bob Bob Designer Designer 65000 65000 Design Design 3

Charlie

Developer

72000

IT 3 3 Charlie Charlie Developer Developer 72000 72000 IT IT 4

Diana

Manager

90000

HR 4 4 Diana Diana Manager Manager 90000 90000 HR HR 5

Eve

Developer

70000

IT 5 5 Eve Eve Developer Developer 70000 70000 IT IT 1. INSERT: Adding Rows to a Table The INSERT command is used to add new rows to a table. Task: Add a new employee, Frank, who is a Tester in the QA department with a salary of $60,000. Task INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
VALUES (6, 'Frank', 'Tester', 60000, 'QA'); INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
VALUES (6, 'Frank', 'Tester', 60000, 'QA'); Result: The table now includes the new employee: Result EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

70000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

72000

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

70000

IT



6

Frank

Tester

60000

QA EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

70000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

72000

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

70000

IT



6

Frank

Tester

60000

QA EmployeeID

Name

Position

Salary

Department EmployeeID EmployeeID Name Name Position Position Salary Salary Department Department 1

Alice

Developer

70000

IT 1 1 Alice Alice Developer Developer 70000 70000 IT IT 2

Bob

Designer

65000

Design 2 2 Bob Bob Designer Designer 65000 65000 Design Design 3

Charlie

Developer

72000

IT 3 3 Charlie Charlie Developer Developer 72000 72000 IT IT 4

Diana

Manager

90000

HR 4 4 Diana Diana Manager Manager 90000 90000 HR HR 5

Eve

Developer

70000

IT 5 5 Eve Eve Developer Developer 70000 70000 IT IT 6

Frank

Tester

60000

QA 6 6 Frank Frank Tester Tester 60000 60000 QA QA 2. UPDATE: Modifying Existing Rows The UPDATE command allows you to modify data in existing rows based on specific conditions. Task: Give all Developers in the IT department a 10% salary increase. Task UPDATE Employees
SET Salary = Salary * 1.10
WHERE Position = 'Developer' AND Department = 'IT'; UPDATE Employees
SET Salary = Salary * 1.10
WHERE Position = 'Developer' AND Department = 'IT'; Result: The salary for Alice, Charlie, and Eve has been updated: Result EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

77000

IT



6

Frank

Tester

60000

QA EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

77000

IT



6

Frank

Tester

60000

QA EmployeeID

Name

Position

Salary

Department EmployeeID EmployeeID Name Name Position Position Salary Salary Department Department 1

Alice

Developer

77000

IT 1 1 Alice Alice Developer Developer 77000 77000 IT IT 2

Bob

Designer

65000

Design 2 2 Bob Bob Designer Designer 65000 65000 Design Design 3

Charlie

Developer

79200

IT 3 3 Charlie Charlie Developer Developer 79200 79200 IT IT 4

Diana

Manager

90000

HR 4 4 Diana Diana Manager Manager 90000 90000 HR HR 5

Eve

Developer

77000

IT 5 5 Eve Eve Developer Developer 77000 77000 IT IT 6

Frank

Tester

60000

QA 6 6 Frank Frank Tester Tester 60000 60000 QA QA 3. DELETE: Removing Rows From a Table The DELETE command removes rows from a table based on a condition. Task: Remove all employees in the QA department. Task DELETE FROM Employees
WHERE Department = 'QA'; DELETE FROM Employees
WHERE Department = 'QA'; Result: Frank has been removed from the table: Result EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

77000

IT EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Developer

77000

IT EmployeeID

Name

Position

Salary

Department EmployeeID EmployeeID Name Name Position Position Salary Salary Department Department 1

Alice

Developer

77000

IT 1 1 Alice Alice Developer Developer 77000 77000 IT IT 2

Bob

Designer

65000

Design 2 2 Bob Bob Designer Designer 65000 65000 Design Design 3

Charlie

Developer

79200

IT 3 3 Charlie Charlie Developer Developer 79200 79200 IT IT 4

Diana

Manager

90000

HR 4 4 Diana Diana Manager Manager 90000 90000 HR HR 5

Eve

Developer

77000

IT 5 5 Eve Eve Developer Developer 77000 77000 IT IT 4. MERGE (UPSERT): Combining Insert and Update The MERGE statement is used to insert new rows or update existing rows based on a match condition. This is also known as “upsert”. Task: If an employee with EmployeeID = 5 exists, update their position to “Lead Developer”. Otherwise, insert a new employee. Task MERGE INTO Employees AS Target
USING (SELECT 5 AS EmployeeID, 'Eve' AS Name, 'Lead Developer' AS Position, 80000 AS Salary, 'IT' AS Department) AS Source
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Position = Source.Position, Salary = Source.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Position, Salary, Department)
    VALUES (Source.EmployeeID, Source.Name, Source.Position, Source.Salary, Source.Department); MERGE INTO Employees AS Target
USING (SELECT 5 AS EmployeeID, 'Eve' AS Name, 'Lead Developer' AS Position, 80000 AS Salary, 'IT' AS Department) AS Source
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Position = Source.Position, Salary = Source.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Position, Salary, Department)
    VALUES (Source.EmployeeID, Source.Name, Source.Position, Source.Salary, Source.Department); Result: Eve’s position has been updated to “Lead Developer”: Result EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Lead Developer

80000

IT EmployeeID

Name

Position

Salary

Department



1

Alice

Developer

77000

IT



2

Bob

Designer

65000

Design



3

Charlie

Developer

79200

IT



4

Diana

Manager

90000

HR



5

Eve

Lead Developer

80000

IT EmployeeID

Name

Position

Salary

Department EmployeeID EmployeeID Name Name Position Position Salary Salary Department Department 1

Alice

Developer

77000

IT 1 1 Alice Alice Developer Developer 77000 77000 IT IT 2

Bob

Designer

65000

Design 2 2 Bob Bob Designer Designer 65000 65000 Design Design 3

Charlie

Developer

79200

IT 3 3 Charlie Charlie Developer Developer 79200 79200 IT IT 4

Diana

Manager

90000

HR 4 4 Diana Diana Manager Manager 90000 90000 HR HR 5

Eve

Lead Developer

80000

IT 5 5 Eve Eve Lead Developer Lead Developer 80000 80000 IT IT 5. TRUNCATE: Quickly Clearing All Rows The TRUNCATE command removes all rows from a table, but unlike DELETE, it does not log individual row deletions, making it faster. Task: Clear all rows from the Employees table. Task TRUNCATE TABLE Employees; TRUNCATE TABLE Employees; Result: The table is now empty, but the structure remains intact. Result 6. DROP: Removing the Entire Table The DROP command deletes a table and its data permanently. Task: Remove the Employees table from the database. Task DROP TABLE Employees; DROP TABLE Employees; Result: The Employees table no longer exists. Result Summary SQL provides a wide range of commands to modify data and table structures. Here’s a quick recap: Command Use Case Command Use Case INSERT: Add new rows to a table.
UPDATE: Modify data in existing rows.
DELETE: Remove specific rows from a table.
MERGE: Combine insert and update logic (upsert).
TRUNCATE: Quickly clear all rows in a table.
DROP Remove the entire table structure and data. INSERT: Add new rows to a table. UPDATE: Modify data in existing rows. DELETE: Remove specific rows from a table. MERGE: Combine insert and update logic (upsert). TRUNCATE: Quickly clear all rows in a table. DROP Remove the entire table structure and data. These commands allow you to keep your database up-to-date, clean, and well-organized. Practice these examples on your own database to gain confidence in modifying data with SQL! Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋 LinkedIn

SQL Data Modification Commands With Examples: A Fast and Easy Guide

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A Quick Guide to Listing All Measures in Power BI via DAX Studio

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

A Quick Guide to Listing All Measures in Power BI via DAX Studio

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps