CRUD with SQL›Core· 30 min read

UPDATE & DELETE

Change existing rows with UPDATE and remove them with DELETE — and why the WHERE clause is essential so you only affect the rows you mean to.

What you will learn

Update rows with UPDATE
Remove rows with DELETE
Always use WHERE

UPDATE

Once data is in a table you often need to change it — fix a typo, set a new price, update someone’s city. That is the U (Update) in CRUD, done with UPDATE. You name the table, list the columns to change with SET, and — crucially — say which rows to change with WHERE.

An UPDATE follows three steps:

Name the table to change: UPDATE users.
List the new values with SET city = 'Mumbai', age = 23 (separate each change with a comma).
Pick the rows to change with WHERE id = 1 — without this, every row is changed.

UPDATE ... SET ... WHERE

UPDATE users
SET city = 'Mumbai', age = 23
WHERE id = 1;

This finds the single row whose id is 1 (Asha) and sets her city to Mumbai and her age to 23 in one statement. Because WHERE id = 1 matches exactly one row, only that row changes — everyone else is left untouched.

Note: Output: Query OK, 1 row affected. MySQL tells you how many rows it changed — here, 1. If the WHERE had matched three rows, it would say "3 rows affected". A row that already had those values may show "0 rows affected".

DELETE

To remove rows entirely you use DELETE FROM — the D (Delete) in CRUD. Just like UPDATE, you must add a WHERE to say which rows to remove.

DELETE a specific row

DELETE FROM users WHERE id = 5;

This removes only the row whose id is 5. The row is gone for good — there is no undo button in SQL — so always double-check your WHERE before running a DELETE.

Note: Output: Query OK, 1 row affected. One matching row was deleted. If no row had id = 5, you would see "0 rows affected" and nothing would change.

Watch out: NEVER run UPDATE or DELETE without a WHERE clause — DELETE FROM users; deletes every row, and an UPDATE with no WHERE changes them all. Always include WHERE to target specific rows.

Q. What happens if you run DELETE FROM users; with no WHERE?

Answer: Without WHERE, DELETE removes ALL rows. Always use WHERE to target specific records.

✍️ Practice

Update one user’s city by id.
Delete a specific row by id (use WHERE!).

🏠 Homework

Practise updating and deleting products — always with a WHERE clause.