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 in one query:

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

I know that I can split this into two separate requests, and it works that way, but I was wondering if it is possible to do this with a single request.

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 , but I used the sentence above to fit your logic.

Answer 2

Using the CASE keyword will work:

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

Answer 3

Using Case Help You

 UPDATE fruits SET ToBuy = CASE when Price < 100 Then 'yes' ELSE 'no' 

Answer 4

You can call UPDATE twice.

 UPDATE fruits SET ToBuy='yes' WHERE Price < 100; UPDATE fruits SET ToBuy='no' WHERE Price >= 100;