Changing data safely
Insert, update and delete without losing rows you meant to keep.
- Write targeted UPDATE and DELETE
- Wrap changes in a transaction
- Preview the affected rows first
A data change is only as safe as its WHERE clause. Preview the rows, run the statement inside a transaction and commit only after checking the count.
BEGIN;
INSERT INTO lessons (id, course_slug, title, minutes, level)
VALUES (11, 'sql', 'Transactions', 16, 'Advanced');
UPDATE lessons
SET minutes = minutes + 2
WHERE course_slug = 'sql';
SELECT id, title, minutes
FROM lessons
WHERE course_slug = 'sql';
ROLLBACK;- 01Select first
Run the same WHERE with SELECT and count the rows.
- 02Begin
Start a transaction so the change can be undone.
- 03Change
Run INSERT, UPDATE or DELETE with the narrowest WHERE.
- 04Verify
Select the affected rows and check the counts.
- 05Commit or roll back
Commit when correct; roll back on any doubt.
Insert, update and roll back
In one transaction, insert a lesson called Indexes with 13 minutes, add 1 minute to it, then roll back.
BEGIN;
-- insert and update here
ROLLBACK;What does ROLLBACK do inside a transaction?
Make it yours.
Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.
Lock in what you just learned.
Mark this lesson complete to save your place and move one step closer to the certificate.