This content originally appeared on HackerNoon and was authored by Luca Liu
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!👋
This content originally appeared on HackerNoon and was authored by Luca Liu
Luca Liu | Sciencx (2025-01-22T01:10:39+00:00) SQL Data Modification Commands With Examples: A Quick and Simple Guide. Retrieved from https://www.scien.cx/2025/01/22/sql-data-modification-commands-with-examples-a-quick-and-simple-guide/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.