A Quick Guide to SQL — Chapter 6: Modifying Data

3 min readJun 20, 2023

In the previous chapter we talked about aggregating data. In this lesson, we will dive into modifying data in SQL. We will explore three essential statements: INSERT, UPDATE, and DELETE. These statements allow us to add, modify, and remove data from tables in a SQL database. By the end of this lesson, you will have a solid understanding of how to modify data effectively using SQL. Let’s get started!

Chapters of This Series

1. Adding Records with INSERT

The INSERT statement is used to add new records to a table. Let’s assume we have a table called “employees” with columns “id,” “name,” and “salary.” To insert a new employee record into the table, use the following syntax:

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 5000);

This statement inserts a new employee record with an ID of 1, name ‘John Doe’, and a salary of 5000 into the “employees” table.

2. Updating Records with UPDATE

The UPDATE statement is used to modify existing records in a table. Let’s assume we want to update the salary of an employee with ID 1. Use the following syntax:

UPDATE employees
SET salary = 6000
WHERE id = 1;

This statement updates the salary of the employee with ID 1 to 6000 in the “employees” table.

3. Deleting Records with DELETE

The DELETE statement is used to remove records from a table. Let’s assume we want to delete the employee with ID 1 from the “employees” table. Use the following syntax:

DELETE FROM employees
WHERE id = 1;

This statement deletes the employee record with ID 1 from the “employees” table.

4. Modifying Multiple Records with UPDATE and DELETE

The UPDATE and DELETE statements can be used to modify multiple records simultaneously. For example, to update the salaries of all employees with a salary less than 5000, use the following syntax:

UPDATE employees
SET salary = salary * 1.1
WHERE salary < 5000;

This statement increases the salaries of all employees with a salary less than 5000 by 10% in the “employees” table.

Similarly, to delete all employees with a salary greater than 10000, use the following syntax:

DELETE FROM employees
WHERE salary > 10000;

This statement removes all employee records with a salary greater than 10000 from the “employees” table.

5. Modifying Data from Another Table

You can also use the INSERT INTO…SELECT statement to insert data from one table into another or modify data based on data from another table. For example, let’s insert the records from the “old_employees” table into the “new_employees” table:

INSERT INTO new_employees (id, name, salary)
SELECT id, name, salary
FROM old_employees;

This statement inserts the records from the “old_employees” table into the “new_employees” table, preserving the same columns and data.

6. Conclusion

In this lesson, we explored the fundamental SQL statements for modifying data in a database. We learned how to add new records using INSERT, update existing records using UPDATE, and remove unwanted records using DELETE. We also saw how to modify multiple records at once and perform data modifications based on data from another table. With these powerful tools, you can effectively manage and modify your database data to keep it up to date and accurate.

If you found this course helpful and would like to explore more free courses, I invite you to follow my account on Medium and connect with me on LinkedIn. I regularly share valuable content on these platforms.