C · WriteIntermediate35 min read
8. INSERT, UPDATE, DELETE safely
SQL · 48 pages source format
Never UPDATE/DELETE without WHERE. SELECT first to preview rows. Use transactions for multi-step changes.
What you'll learn
- INSERT
- UPDATE ... WHERE
- DELETE ... WHERE
- Safety ritual
INSERT
INSERT INTO users (name, email) VALUES (...). INSERT ... SELECT for bulk copies.
INSERT INTO users (name, email, active)
VALUES ('Test User', 'test@example.com', 1);Do this now
Insert 3 test rows into a sandbox table.
Clear?
Safety ritual
1) SELECT with same WHERE. 2) Check row count. 3) UPDATE/DELETE in transaction. 4) COMMIT or ROLLBACK.
- SELECT preview with identical WHERE
- Confirm expected row count
- BEGIN transaction
- COMMIT if correct, ROLLBACK if not
Do this now
Document your 4-step ritual in sql-notes/SAFETY.md.
Clear?
UPDATE and DELETE
UPDATE users SET active = 0 WHERE id = 5. DELETE FROM sessions WHERE expired_at < NOW(). Always WHERE.
Do this now
Practice UPDATE + ROLLBACK on toy data.
Pro tip. Production horror story: UPDATE without WHERE updates every row.
Clear?