SQL and MySQL course Β· Module 5: Modifying data (DML)
DELETE - taking a scroll off the shelf
In this lesson3
When a book is destroyed or a reader withdraws from the Library, the scribe removes the matching row with the DELETE FROM command.
Syntax
DELETE FROM czytelnicy
WHERE id = 12;Two parts:
- DELETE FROM czytelnicy - which shelf we are removing from.
- WHERE id = 12 - which row is to disappear.
Notice that with DELETE we do not provide any columns - we remove the whole row, not a single field. (To clear just one field, we use UPDATE ... SET field = NULL.)
Removing many rows
WHERE may point to more than one row:
DELETE FROM ksiazki
WHERE dostepna = 0;This will remove all books marked as unavailable.
SECOND GREAT WARNING: always WHERE!
This command looks harmless, yet it is the most dangerous of them all:
DELETE FROM czytelnicy;Without WHERE it removes ALL readers - the whole shelf is emptied! There is no trash bin, no undo. The scrolls are gone.
That is why an experienced scribe has an iron habit: write WHERE first, only then the rest of the command. Some even check the condition with SELECT * FROM table WHERE ... ended with a ";" to see which rows will disappear before they issue the DELETE.
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 will the command do: DELETE FROM czytelnicy; (without a WHERE clause)?
2. Why do we not provide column names with DELETE?
Hands-on tasks in the game
- Code editor
Write a command that removes from the czytelnicy table only one reader - the one with id = 12. Be sure to use the WHERE clause.
- Horizontal ordering
Arrange the query elements on one line in the correct order (left to right):
- Horizontal ordering
Arrange on one line the command that removes one row (left to right):
- Code editor
Write a command that removes from the wypozyczenia table only the entry with id = 3. Be sure to use the WHERE clause.