Share this article

Join our newsletter

Data Manipulation in SQL – INSERT, UPDATE, and DELETE Essentials

Learn how to modify your data using SQL’s core Data Manipulation Language (DML) commands: INSERT, UPDATE, and DELETE.

SQL Data Manipulation Operations

INSERT

INSERT INTO employees (id, name, department, salary)
VALUES (4, ‘Alice’, ‘Marketing’, 65000);

id	name	department	salary
1	John	IT	75000
2	Sarah	HR	65000
3	Mike	Sales	70000

➜

id	name	department	salary
1	John	IT	75000
2	Sarah	HR	65000
3	Mike	Sales	70000
4	Alice	Marketing	65000

UPDATE

UPDATE employees
SET salary = 80000
WHERE department = ‘IT’;

id	name	department	salary
1	John	IT	75000
2	Sarah	HR	65000
3	Mike	Sales	70000
4	Alice	Marketing	65000

➜

id	name	department	salary
1	John	IT	80000
2	Sarah	HR	65000
3	Mike	Sales	70000
4	Alice	Marketing	65000

DELETE

DELETE FROM employees
WHERE department = ‘Marketing’;

id	name	department	salary
1	John	IT	80000
2	Sarah	HR	65000
3	Mike	Sales	70000
4	Alice	Marketing	65000

➜

id	name	department	salary
1	John	IT	80000
2	Sarah	HR	65000
3	Mike	Sales	70000
4	Alice	Marketing	65000

Introduction

After learning how to retrieve and organize data using SELECT statements, filtering, grouping, and joining, the next essential step is learning how to modify your data. SQL’s Data Manipulation Language (DML) includes the INSERT, UPDATE, and DELETE commands that allow you to add, change, and remove data from your tables.

In this article, you will learn:

INSERT: How to add new rows to a table.

UPDATE: How to modify existing data.

DELETE: How to remove unwanted data.

Best practices: Techniques to ensure data integrity and maintain performance.

For foundational concepts, feel free to revisit our Basic SQL SELECT Statement and Filtering Data in SQL – Mastering the WHERE Clause articles. And if you’re looking for further training, our comprehensive SQL course is a great next step.

Inserting Data with INSERT

The INSERT command is used to add new rows to a table. Here’s the basic syntax:

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

Example: Inserting a New Employee Record

Imagine you have an employees table with columns first_name, last_name, and department_id. To insert a new employee:

INSERT INTO employees (first_name, last_name, department_id) VALUES (‘John’, ‘Doe’, 3);

Explanation:

This command adds a new record to the employees table with the specified values.

Updating Data with UPDATE

The UPDATE command modifies existing records in a table. It is important to always use a WHERE clause to specify which rows should be updated; otherwise, all rows in the table will be affected.

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

Example: Updating an Employee’s Department

To change the department of an employee with an employee_id of 101:

UPDATE employees SET department_id = 4 WHERE employee_id = 101;

Explanation:

This query updates the department_id for the employee with the specified employee_id.

Deleting Data with DELETE

The DELETE command removes rows from a table. As with UPDATE, using a WHERE clause is critical to avoid deleting all records unintentionally.

DELETE FROM table_name WHERE condition;

Example: Deleting an Employee Record

To remove an employee with an employee_id of 101:

DELETE FROM employees WHERE employee_id = 101;

Explanation:

This command deletes the row in the employees table where the employee_id is 101.

Best Practices for Data Manipulation

Always Use a WHERE Clause: Prevent accidental updates or deletions by specifying exact conditions.

Backup Your Data: Before running data manipulation commands on production data, back up your tables to avoid data loss.

Test Your Queries: Run your queries in a test environment to ensure they work as expected.

Monitor Performance: Particularly with large datasets, monitor the performance impact of your DML commands and consider indexing key columns.

Conclusion

Mastering the INSERT, UPDATE, and DELETE commands is crucial for effective data management in SQL. These DML commands empower you to modify your datasets, keeping your database up-to-date and reflective of current information. With these skills, you can ensure your data remains accurate and relevant.

Stay tuned for our next article: “Defining Your Database – SQL Data Definition Language (DDL) Basics“, where we explore how to create and manage your database structures.

Have questions or tips about data manipulation in SQL? Leave a comment below or join our community for more interactive learning and support.