A Quick Guide to SQL Data Modification Commands with Examples

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:

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:

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:

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:

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”:

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

1. INSERT Add new rows to a table.
2. UPDATE Modify data in existing rows.
3. DELETE Remove specific rows from a table.
4. MERGE Combine insert and update logic (upsert).
5. TRUNCATE Quickly clear all rows in a table.
6. 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!

Explore more

Luca LiuFollow

Hello there! 👋 I'm Luca, a Business Intelligence Developer with passion for all things data. Proficient in Python, SQL, Power BI, Tableau, SAP Business Objects.

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn