Data Manipulation Basics

Manipulating Your Data

So far, you've learned how to ask questions of your database using SELECT. Now it's time to learn how to change the data itself. Modifying data is a core part of managing any database, and it's handled by three main commands: INSERT, UPDATE, and DELETE. These commands allow you to add new rows, change existing ones, and remove them entirely.

CRUD stands for Create, Read, Update, Delete, the four basic operations you can perform on data in any database.

Let's look at each of these operations. We'll use a simple Employees table as our example throughout.

EmployeeID	FirstName	LastName	Department	HireDate
101	Alice	Smith	Sales	2022-03-15
102	Bob	Johnson	IT	2021-07-22

Adding New Records

To add a new row of data to a table, you use the INSERT INTO statement. Its job is to create a new record and populate it with the values you provide.

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

You specify the table, list the columns you want to fill, and then provide the corresponding values in the VALUES clause. The order of the values must match the order of the columns you listed.

Let's add a new employee named Charlie Brown to our Employees table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, HireDate)
VALUES (103, 'Charlie', 'Brown', 'Marketing', '2023-01-10');

After running this command, our table now has a new row for Charlie.

EmployeeID	FirstName	LastName	Department	HireDate
101	Alice	Smith	Sales	2022-03-15
102	Bob	Johnson	IT	2021-07-22
103	Charlie	Brown	Marketing	2023-01-10

Modifying Existing Records

When you need to change data that's already in a table, you use the UPDATE statement. This could be for anything from correcting a typo to updating a status.

A crucial part of the UPDATE statement is the WHERE clause. It specifies exactly which row or rows you want to change. If you forget it, you'll update every single row in the table, which is rarely what you want.

Always double-check your WHERE clause before running an UPDATE command. It's the safety latch that prevents widespread data changes.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

Let's say Alice Smith moves from the Sales department to Marketing.

UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeID = 101;

This command finds the row where EmployeeID is 101 and sets the Department column to 'Marketing' for that specific row only. All other rows are left untouched.

Removing Records

To permanently remove one or more rows from a table, you use the DELETE FROM statement. Just like with UPDATE, the WHERE clause is essential here. Without it, you will delete every row in your table.

There is no undo button for a DELETE command. Once the data is gone, it's gone for good, unless you have a backup.

DELETE FROM table_name
WHERE condition;

Imagine employee Bob Johnson leaves the company. We can remove his record from the table like this:

DELETE FROM Employees
WHERE EmployeeID = 102;

This command finds the row for employee 102 and removes it entirely. The Employees table now looks like this:

EmployeeID	FirstName	LastName	Department	HireDate
101	Alice	Smith	Marketing	2022-03-15
103	Charlie	Brown	Marketing	2023-01-10

Best Practices

Manipulating data is powerful, and with great power comes great responsibility. Here are a few tips to keep your data safe and consistent:

Always use WHERE: For UPDATE and DELETE, be specific about which rows you're targeting. Forgetting the WHERE clause is one of the most common and destructive mistakes.
Test with SELECT: Before running an UPDATE or DELETE, write a SELECT statement with the same WHERE clause. This lets you preview exactly which rows will be affected. If the SELECT query returns the rows you expect, you can confidently proceed with your modification.
Wrap in a transaction: For complex operations involving multiple steps, use transactions. A transaction bundles multiple commands into a single, all-or-nothing operation. If one step fails, you can roll back the entire set of changes, preserving the integrity of your data. We'll cover transactions in more detail later.

Quiz Questions 1/6

Which SQL command is used to add a new row of data into a table?

Quiz Questions 2/6

What is the most likely outcome of running an UPDATE statement without a WHERE clause?

Mastering these three commands gives you full control to manage the data within your database tables.

SQL Data Management Fundamentals