UPDATE & DELETE

INSERT gets rows in and SELECT gets them back out - UPDATE and DELETE are how you change or remove rows that are already sitting in a StreamHub table. Both statements are simple to write and genuinely dangerous to get wrong, for exactly the same reason: they act on every row that matches their WHERE clause, and if you forget that clause, "every row that matches" quietly becomes "every row."

The UPDATE Syntax

Say a data-entry mistake left a StreamHub movie with the wrong runtime. UPDATE fixes it in place:

UPDATE movies
SET duration_minutes = 124
WHERE id = 3;

Only the row with id = 3 changes; every other movie's runtime is untouched.

Updating Multiple Columns at Once

A single UPDATE can set several columns together, separated by commas:

UPDATE movies
SET title = 'Harbor Lights (Director\'s Cut)', duration_minutes = 131
WHERE id = 3;

Why WHERE Is the Most Important Part of Both Statements

Here's the exact same UPDATE from above, with the WHERE clause missing:

UPDATE movies
SET duration_minutes = 124;

The same danger applies to DELETE, just more permanently:

DELETE FROM movies;

A Genuinely Useful Habit: SELECT First

Before running an UPDATE or DELETE whose blast radius you're not completely sure of, run the equivalent SELECT with the exact same WHERE clause first. It shows you precisely which rows would be affected, with zero risk, before you commit to changing anything:

-- Step 1: see exactly what this would touch
SELECT * FROM movies WHERE release_year < 2000;

-- Step 2: only one row matched, so the real statement is safe to run
DELETE FROM movies WHERE release_year < 2000;

That one extra SELECT turns "I think this only affects one row" into "I confirmed this only affects one row" - a habit worth keeping for the rest of your SQL career.

UPDATE / DELETE With a Subquery

WHERE clauses can also check against the result of a separate query. A realistic StreamHub cleanup task: a movie was removed from the catalog, but users may still have it sitting in their watchlist. This deletes exactly those now-orphaned watchlist entries:

DELETE FROM watchlist
WHERE movie_id NOT IN (SELECT id FROM movies);

The inner query returns every id that still legitimately exists in movies; the outer DELETE removes any watchlist row whose movie_id isn't in that list. Subqueries, later in this track, covers this whole pattern - queries nested inside other queries - in much greater depth.

Wrapping a Risky Change in a Transaction

For an UPDATE or DELETE you're genuinely nervous about, MySQL lets you wrap it in a transaction, look at the result, and back out entirely if something's wrong:

START TRANSACTION;
DELETE FROM watchlist WHERE movie_id NOT IN (SELECT id FROM movies);
-- check that the right number of rows were affected, then either:
COMMIT;
-- or, if something looks off:
-- ROLLBACK;

We cover transactions and the guarantees behind COMMIT and ROLLBACK properly in Transactions & ACID - for now, just know this safety net exists for exactly the kind of change this lesson has been warning about.