Aiming with the WHERE clause - conditions in DML

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.

1UPDATE ksiazki
2SET dostepna = 0
3WHERE 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.

1DELETE FROM ksiazki
2WHERE 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?".