SQL and MySQL course Β· Module 11: Capstone project
The Scriptorium - UPDATE and DELETE
In this lesson3
Knowledge is alive, so data must be corrected and sometimes deleted. In the Scriptorium scribes corrected scrolls - we do it with the UPDATE and DELETE statements.
UPDATE - changing values
UPDATE changes existing rows. After SET we give the new values, and WHERE points to which rows to correct:
UPDATE ksiazki SET cena = 19.99 WHERE id = 5;DELETE - removing rows
DELETE removes rows that meet a condition:
DELETE FROM ksiazki WHERE id = 7;The golden rule: always WHERE!
This is the scribe's most important warning. If you forget the WHERE clause:
UPDATE ksiazki SET cena = 0; -- changes the price of ALL books!
DELETE FROM ksiazki; -- removes ALL books!Without WHERE the statement affects the whole shelf. This is one of the most dangerous mistakes when working with a database. Before you run UPDATE or DELETE, make sure you have added a condition pointing to the right rows. Caution is the mark of a master!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What happens when you run UPDATE without a WHERE clause?
2. Which statement do we use to change values in existing rows?
Hands-on tasks in the game
- Code editor
Write an UPDATE statement that in the ksiazki table sets cena to 19.99 only for the book with id equal to 5.
- Code editor
Write a DELETE statement that removes from the ksiazki table only the row with id equal to 7.
- Click in order
Click the elements in order to build a safe price-change statement: