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. 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 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. INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
VALUES (6, 'Frank', 'Tester', 60000, 'QA'); Result: The table now includes the new employee: 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 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. UPDATE Employees
SET Salary = Salary * 1.10
WHERE Position = 'Developer' AND Department = 'IT'; Result: The salary for Alice, Charlie, and Eve has been updated: 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 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. DELETE FROM Employees
WHERE Department = 'QA'; Result: Frank has been removed from the table: 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 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. 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”: 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 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. TRUNCATE TABLE Employees; Result: The table is now empty, but the structure remains intact. 6. DROP: Removing the Entire Table The DROP command deletes a table and its data permanently. Task: Remove the Employees table from the database. DROP TABLE Employees; Result: The Employees table no longer exists. Summary SQL provides a wide range of commands to modify data and table structures. Here’s a quick recap: 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. 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!👋 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 Quick and Simple Guide

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

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!

10 Best Online Courses to Learn Oracle and PL/SQL for Beginners

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!

10 Best Online Courses to Learn Oracle and PL/SQL for Beginners

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps