How to send this SQL statement in a single query?

Question

I am using MySQL and I want to send a statement similar to this one in a single query :

UPDATE fruits SET ToBuy=’yes’ WHERE Price <100, ToBuy=’no’ WHERE Price >=100

I know that I can divide this in 2 separate queries and it works this way but I was wondering if it is possible to do it with a single query.

Answer 1

You need a CASE statement:

UPDATE fruits
SET ToBuy = CASE WHEN Price < 100 THEN 'yes' WHEN Price >=100 THEN 'no' END

Of course you could just use CASE WHEN Price < 100 THEN 'yes' ELSE 'no' END as well, but I've used the above clause to match your logic.

Answer 2

Using the CASE keyword would work :

UPDATE fruits

SET
   ToBuy = CASE WHEN Price<100 THEN  'yes'
               ELSE 'no'