Working with Single-Table Data

Updating and Deleting Data

---

So far, we’ve worked on read queries and simple inserts. UPDATE and DELETE are different again: they change or remove rows that already exist. We, again, approach this by asking questions about the change we want to make:

• What rows do we want to change or remove?
• Which table are we modifying?
• Which condition targets exactly those rows?
• What will the table look like afterward?
• How can we check the target safely before running the write?

Updating Rows

Suppose we want to change the title of one course.

• Table: courses
• Target row: the course whose code is CS-A1150
• Change: set title to Introduction to SQL

Then the statement can say exactly that:

UPDATE courses
SET title = 'Introduction to SQL'
WHERE code = 'CS-A1150';

This changes only the row whose code is CS-A1150. The title value changes, but the row itself stays in the table.

Existing data is changed with UPDATE. It does not create or remove rows.

Loading Exercise...

Deleting Rows

Sometimes the intention is not to change a row, but to remove it completely. Suppose we want to remove a temporary course whose code is TMP100.

DELETE FROM courses
WHERE code = 'TMP100';

This removes the matching row from the table.

Loading Exercise...

WHERE Matters

With both UPDATE and DELETE, WHERE is what keeps the change narrow. Without WHERE, an update or delete can affect every row in the table. For example, the following statement changes the title of every course to oops:

UPDATE courses
SET title = 'oops';

That statement is syntactically valid. It is also probably a mistake.

Similarly, the following statement removes every row from courses:

DELETE FROM courses;

This is one of the main conceptual differences between write statements and many of the read queries we studied earlier. A small mistake in a write statement does not only produce a confusing result. It can change the stored database state in the wrong way.

Preview Target Rows

A good habit is to write a SELECT with the same condition first, confirm that it matches the intended rows, and only then turn it into UPDATE or DELETE.

For example, suppose we are thinking about deleting the course whose code is TMP100. Before we run the delete, we can check which row matches that code:

SELECT
  id,
  code,
  title
FROM courses
WHERE code = 'TMP100';

If that result looks right, then the matching delete becomes much safer:

DELETE FROM courses
WHERE code = 'TMP100';

The same habit works for updates. First check which rows match the condition. Then run the write.

So the safe pattern is:

1. decide exactly which rows should change
2. write a SELECT with the same WHERE condition
3. confirm that the selected rows are the intended target
4. turn that condition into UPDATE or DELETE
5. verify the new state afterward if needed

Loading Exercise...

Check Your Understanding

1. Why is a missing WHERE clause dangerous in UPDATE and DELETE statements?
2. Why is it good practice to run a matching SELECT before a write operation?
3. What is the difference between changing a row with UPDATE and removing it with DELETE?

SQL Practice

Complete the SQL sequence below to practice careful UPDATE and DELETE statements together with verification queries.

Loading Exercise...