SQL and MySQL course Β· Module 5: Modifying data (DML)

Aiming with the WHERE clause - conditions in DML

2 min read Β·
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. 1. Which operator in a WHERE clause means 'greater than'?

  2. 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.

  1. QuestionThe greater-than operator
  2. QuestionCombining conditions with AND
  3. Coding taskUpdate by condition
  4. Click in orderPrices above 100
  5. QuestionCondition with AND
  6. Coding taskDelete by two conditions

Useful articles