Velamuvi AIAcademy

Lesson 15 of 17 · Changing Data

UPDATE and DELETE

Change and remove data with SQL UPDATE and DELETE. Learn why the WHERE clause is essential, how to check before you change and how transactions protect you.

  • 7 min read
  • Beginner
  • Reviewed
In this lesson you will learn
  • How to change values in existing rows
  • How to remove rows
  • Why forgetting WHERE is the most dangerous mistake in SQL
  • Two habits that keep your data safe

Changing existing rows

UPDATE changes values in rows that already exist. It has three parts: the table, the new values and the rows to change.

Try it yourselfSQL
UPDATE enrollments
SET progress = 100, score = 80
WHERE id = 7;

SELECT id, student_id, course_id, progress, score
FROM enrollments
WHERE id = 7;
  • UPDATE enrollments names the table.
  • SET lists the columns to change, with commas between them.
  • WHERE chooses which rows to change. It works exactly as it does in SELECT.

Using the old value

The new value can be calculated from the current one. This adds two hours to every Programming course.

Try it yourselfSQL
UPDATE courses
SET hours = hours + 2
WHERE category = 'Programming';

SELECT title, category, hours
FROM courses;

The most dangerous mistake in SQL

Leave out WHERE, and the change is applied to every row in the table.

Try it yourselfSQL
UPDATE students
SET country = 'Unknown';

SELECT id, name, country
FROM students;

Every student's country is gone. There was no warning and no "Are you sure?". In a real database, with no backup, that information would be lost for good.

Here the data comes back the next time you press Run. At work it would not.

Habit 1: SELECT first

Before you change anything, run a SELECT with the same WHERE clause. Look at the rows. Are they the ones you mean to change? Is the number what you expected?

Try it yourselfSQL
-- Step 1: look before you change
SELECT id, student_id, progress
FROM enrollments
WHERE progress < 30;

Only when the result is right do you swap SELECT ... FROM for UPDATE ... SET and keep the WHERE untouched.

Removing rows

DELETE FROM removes whole rows. It needs a WHERE clause for the same reason.

Try it yourselfSQL
DELETE FROM enrollments
WHERE progress < 30;

SELECT COUNT(*) AS enrollments_left
FROM enrollments;

One enrollment matched, so 17 of the 18 remain. DELETE removes entire rows. To clear a single value, use UPDATE ... SET column = NULL instead.

Without WHERE, DELETE FROM enrollments; empties the table.

Habit 2: use a transaction

A transaction groups statements so that they can be undone together. Start one with BEGIN. If the result is wrong, ROLLBACK cancels everything since the BEGIN. If it is right, COMMIT makes it permanent.

Try it yourselfSQL
BEGIN;

DELETE FROM students;                              -- a terrible mistake
SELECT COUNT(*) AS during_transaction FROM students;

ROLLBACK;                                          -- undo it

SELECT COUNT(*) AS after_rollback FROM students;

Inside the transaction the table was empty. After the rollback, all twelve students are back. Experienced engineers wrap every risky change in a transaction and check the result before committing.

Deleting rows that other rows point to

Think before deleting a row that other tables refer to. If student 1 is deleted, the enrollments with student_id = 1 point at nobody. Depending on how the database is set up, it either refuses the delete or leaves those orphaned rows behind.

Many systems avoid the problem by never truly deleting. They add a column such as is_active and set it to false, so the history stays intact. This is called a soft delete.

Common mistakes

Practice exercise

Set the country of student 12 to Italy, and make sure no other student is changed.

Your solutionSQL
-- Sofia Rossi (student id 12) tells us she lives in Italy.
-- Write an UPDATE that sets her country. Do not change the SELECT.


SELECT id, name, country
FROM students
WHERE id >= 10
ORDER BY id;
Knowledge check

What does DELETE FROM orders; do?

Summary

  • UPDATE table SET column = value WHERE condition; changes rows.
  • DELETE FROM table WHERE condition; removes rows.
  • Without WHERE, every row is affected.
  • Run a SELECT with the same WHERE first.
  • Use BEGIN, then COMMIT or ROLLBACK, around risky changes.

Next: creating tables of your own.

Finished reading? Tick it off to track your progress.