Lesson 10 — Modifying Data

UPDATE

UPDATE modifies existing rows. The WHERE clause is critical — omitting it updates every row in the table. Always write the WHERE clause before the SET clause.

SQL — Basic UPDATE 
-- Update one column for one row:
UPDATE learners
SET    mark = 82.0
WHERE  learner_id = 1;

-- Update multiple columns:
UPDATE learners
SET    mark   = 82.0,
       city   = 'Cape Town',
       course = 'Python'
WHERE  learner_id = 1;

-- WITHOUT WHERE — updates every single row (usually a catastrophic mistake):
-- UPDATE learners SET mark = 100;  -- gives everyone 100% !!
SQL — Conditional UPDATE 
-- Update based on a condition:
UPDATE learners
SET    is_active = FALSE
WHERE  enrolment_date < '2023-01-01';

-- Update using a calculated value:
UPDATE learners
SET    mark = mark * 1.05        -- give 5% bonus
WHERE  course = 'Java' AND mark < 50;

-- Update using CASE for conditional values:
UPDATE learners
SET    grade_label = CASE
    WHEN mark >= 80 THEN 'Distinction'
    WHEN mark >= 60 THEN 'Merit'
    WHEN mark >= 50 THEN 'Pass'
    ELSE 'Not yet competent'
END
WHERE mark IS NOT NULL;
SQL — Safe UPDATE workflow 
-- Safe UPDATE workflow — ALWAYS preview with SELECT first:
SELECT learner_id, full_name, mark
FROM   learners
WHERE  city = 'Johannesburg' AND mark < 50;
-- Review the result — does this look right? Then run:
UPDATE learners
SET    mark = mark + 5
WHERE  city = 'Johannesburg' AND mark < 50;

-- UPDATE with a subquery:
UPDATE learners
SET    mark = (
    SELECT AVG(mark) FROM learners WHERE course = 'Java'
)
WHERE learner_id = 5 AND mark IS NULL;
-- Sets learner 5's mark to the Java course average

Practice Task

Your Turn

Write UPDATE statements: (1) set the city to 'Pretoria' for learner_id 3; (2) add 2 marks to all learners below 50 (a resubmission bonus); (3) deactivate all learners who enrolled before 2023; (4) update the email for a specific learner. For each, first write the SELECT that previews the affected rows.

Common Mistakes

  • UPDATE without WHERE — the single most dangerous SQL mistake. Always write WHERE first.
  • Updating the primary key — breaks foreign key references in child tables.
  • Updating without checking constraints — NOT NULL, UNIQUE, and CHECK constraints still apply.
  • Batch updates on large tables — lock the table for a long time. Use WHERE to limit rows or update in batches.

Professional Tip

Professional workflow: always write a SELECT with the same WHERE clause first, verify the result set looks right, then change SELECT to UPDATE SET. This single habit prevents most accidental mass-updates.

Mini Quiz

What happens when you run UPDATE without a WHERE clause?