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:
- Select all items and set different values using
Update `table` set `a` = if ( `b`>=100, if (`b`>=1000, ... , 'Less than thousand') , 'Less than hundred' ) where `someid`='1'
- 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?
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.