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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *