nonobie — Real dev skills, explained like a friend would

Warming up the neural circuits...

What you'll learn

In the previous module, we learned how to add and read data. In this module, we tackle the "dangerous" half of : Update and Delete. By the end of this module, you'll know how to modify data without accidentally wiping your entire database.

1. UPDATE — Modifying Existing Data

The UPDATE command changes values in rows that already exist.

sql

UPDATE users
SET age = 26, email = 'alice.new@example.com'
WHERE id = 1;

The "Danger Zone"

If you forget the WHERE clause, will update every single row in the table.

sql

-- DANGER: Every user's age is now 0!
UPDATE users SET age = 0;

Danger

Pro Rule: Never write an UPDATE or DELETE statement without writing the WHERE clause first.

2. DELETE — Removing Data

The DELETE command removes rows permanently.

sql

DELETE FROM users
WHERE id = 4;

Just like UPDATE, a missing WHERE clause is a disaster:

sql

-- DANGER: The table is now empty!
DELETE FROM users;

3. The "Safety Net": Transactions

In a professional environment, you rarely run an UPDATE or DELETE directly. Instead, you wrap them in a . This allows you to "preview" the change and cancel it if it looks wrong.

sql

BEGIN; -- Start the safety net
 
UPDATE users SET age = 99 WHERE id = 1;
 
-- Check if it looks right
SELECT * FROM users WHERE id = 1;
 
-- If yes:
COMMIT; -- Save changes permanently
 
--

Transaction Lifecycle

Transaction Safety Flowflowchart

Rendering diagram…

4. Soft Delete vs. Hard Delete

In modern applications (like Facebook or Instagram), data is rarely actually deleted. Instead, we use a Soft Delete.

Strategy	Command	Use Case
Hard Delete	`DELETE FROM...`	Removing temporary data, clearing logs, GDPR compliance.
Soft Delete	`UPDATE table SET deleted_at = NOW()...`	Almost everything else (Users, Posts, Orders). Allows recovery.

Implementing a Soft Delete

sql

-- First, add a column to your table
-- ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
 
-- To "delete" a user:
UPDATE users 
SET is_active = false 
WHERE id = 5;
 
-- To query "non-deleted" users:
SELECT * FROM users WHERE is_active = true;

Practice Exercise

Update the price of one course in your courses table using its id.
Try updating the is_published status for multiple courses using a condition (e.g., WHERE price > 2000).
Safe Test:
- Start a transaction with BEGIN.
- Delete all courses.
- Run SELECT * FROM courses to confirm they are gone.
- Run ROLLBACK.
- Run SELECT * FROM courses to confirm they are back!

Note

Security Tip: In many companies, "Junior" developers are not given permissions to run DELETE commands on production databases. Mastering transactions is the first step toward earning that trust.

Module 5 — Modifying Data Safely