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
- 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.
UPDATE enrollments SET progress = 100, score = 80 WHERE id = 7; SELECT id, student_id, course_id, progress, score FROM enrollments WHERE id = 7;
UPDATE enrollmentsnames the table.SETlists the columns to change, with commas between them.WHEREchooses which rows to change. It works exactly as it does inSELECT.
Using the old value
The new value can be calculated from the current one. This adds two hours to every Programming course.
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.
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?
-- 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.
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.
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
Set the country of student 12 to Italy, and make sure no other student is changed.
-- 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;
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
SELECTwith the sameWHEREfirst. - Use
BEGIN, thenCOMMITorROLLBACK, around risky changes.
Next: creating tables of your own.
Finished reading? Tick it off to track your progress.