One if or multiple where?

Let’s say, I want to set a value to multiple rows depending on the value of another cell. So, I can make this in 2 ways:

1. Select all items and set different values using IF:

Update `table` set `a` = 
    if (
        `b`>=100,  
        if (`b`>=1000, ... , 'Less than thousand') , 
        'Less than hundred'
    ) 
where `someid`='1' 

2. Or do several updates:

Update `table` set `a` = 'Less than hundred'  where `someid`='1' and `b`<100;
Update `table` set `a` = 'Less than thousand' where `someid`='1' and `b`<1000 and `b`>=100;
...

Which one will be better practice in performance?

Answer

I would recommend running a single query, and using CASE insteaf of IF to make the syntax easier to follow.

Running multiple queries mean multiple table scans, whereas this query does just one scan.

UPDATE mytable 
SET a = CASE
    WHEN b < 100 THEN 'Less than an hundred'
    WHEN b < 1000 THEN 'Less than an thousand'
    ...
END

With an index on b, this should be very efficient.