How to compose a query for multiple update mysql fields?

Question

MySQL

Hello, there is a simple table with id and float number. It is necessary to select 5 elements with the largest number, reduce it by a factor (say 100), and assign the value 1 to all other elements of the table. Is it better to organize this mass update of the table - through a loop or are there other solutions? please help me make a request.

Answer 1

INSERT INTO `table` (`id_lead`,`value`)VALUES (1,'test'),(3,'hello') ON DUPLICATE KEY UPDATE value=VALUES(value)

Answer 2

begin;
create temporary table max5float select id as maxid from `table` order by float desc limit 5; -- проверьте что тут будет, я постоянно путаю направления сортировки
update `table` left join max5float on id=maxid set `float`=if(maxfloat is null, 1, `float`*100);
drop temporary table max5float;
commit;

The bottom line - we make a temporary table, where we throw off the IDs of the maximum 5 elements, then we update - if there is such a row, then we multiply by the coefficient, if there is no such row in the temporary table, then we assign 1.

Answer 3

scherbanich - i.e. loop I only form the final request - right?