SQL and MySQL course Β· Module 5: Modifying data (DML)
Aiming with the WHERE clause - conditions in DML
In this lesson3
You already know INSERT, UPDATE and DELETE. Now I will teach you precise aiming - because it is the WHERE clause that decides which scrolls will be changed or removed.
Comparison operators
In a WHERE condition we compare a column with a value. The scribe's most common operators:
=- equal (e.g.WHERE id = 5),>- greater than (e.g.WHERE cena > 100),<- less than (e.g.WHERE rok_wydania < 0),>=,<=- greater/less than or equal,<>(or!=) - different from.
UPDATE ksiazki
SET dostepna = 0
WHERE cena > 100;This command will mark as unavailable all books more expensive than 100 - it could be one row or a hundred. WHERE covers every matching scroll.
Combining conditions: AND and OR
Sometimes one condition is not enough. The scribe joins them with connectors:
- AND - both conditions must be met at once,
- OR - one of the conditions is enough.
DELETE FROM ksiazki
WHERE dostepna = 0 AND autor_id = 4;This will remove only those books that are at once unavailable and written by author number 4. If we used OR, books meeting either condition would disappear - many more scrolls!
Aim consciously
The more precise the WHERE, the safer the command. A wrong value after > or confusing AND with OR can cover hundreds of rows you never meant to touch. Before you run an UPDATE or DELETE, read your condition aloud: "are these exactly the rows I mean?".
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. Which operator in a WHERE clause means 'greater than'?
2. What does WHERE kraj = 'Egipt' AND rok_urodzenia > 0 mean in an UPDATE/DELETE command?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a command that in the ksiazki table sets dostepna to 0 for all books with cena greater than 100. Use a WHERE clause with the > operator.
- Click in order
Click the elements in the order in which we build a simple WHERE condition:
- Code editor
Write a command that removes from the ksiazki table books that are unavailable (dostepna = 0) AND come from autor_id = 4. Use WHERE with AND.