1

Topic: Help to optimize UPDATE

Colleagues help, whether it is possible UPDATE to wrap in one the code.
The table grid the such.
idwork, idday, h0, h1, h2, h3, h4
25,105,0,0,5,1,0
25,106,0,0,0,0,5
25,107,0,0,2,1,0
35,105,5,0,0,2,0
35,106,0,4,0,0,5
35,107,0,0,5,5,0
It is necessary 5 to replace all with zero in idwork=35. Other digits not to touch. It is a lot of lines, and h0... h23, too it is a lot of them. At me it turns out not beautifully. A cycle in the lines (now them here 3 lines idwork=35) and then a cycle 0. 23, total it turns out 72 UPDATE! (((((

UPDATE grid SET h0=0 WHERE idwork=35 AND idday=105 AND h0=5
UPDATE grid SET h1=0 WHERE idwork=35 AND idday=105 AND h1=5
UPDATE grid SET h2=0 WHERE idwork=35 AND idday=105 AND h2=5
... And so 72 times

And can be under one hundred-two lines idwork=35 that there will be generally a nightmare with amount UPDATE.
The basis is designed so, it not  about the new.
How it is possible to solve the task beautifully?

2

Re: Help to optimize UPDATE

f50 wrote:

it is necessary 5 to replace all with zero in idwork=35.

+ idday=105?

3

Re: Help to optimize UPDATE

UPDATE grid
SET h0=CASE h0 WHEN 5 THEN 0 ELSE h0 END
, h1=CASE h1 WHEN 5 THEN 0 ELSE h1 END
, h2=CASE h2 WHEN 5 THEN 0 ELSE h2 END
, h3=CASE h3 WHEN 5 THEN 0 ELSE h3 END
, h4=CASE h4 WHEN 5 THEN 0 ELSE h4 END
WHERE idwork=35
AND idday=105

4

Re: Help to optimize UPDATE

Akina wrote:

UPDATE grid
SET h0=CASE h0 WHEN 5 THEN 0 ELSE h0 END
, h1=CASE h1 WHEN 5 THEN 0 ELSE h1 END
, h2=CASE h2 WHEN 5 THEN 0 ELSE h2 END
, h3=CASE h3 WHEN 5 THEN 0 ELSE h3 END
, h4=CASE h4 WHEN 5 THEN 0 ELSE h4 END
WHERE idwork=35
AND idday=105

The class, learned that so it is possible, thanks!!! UPDATE lines are possible so much, how many, it is already normal. Though I already thought structure all the same to change, add a column hour, and all the question is settled. But I will better leave structure as was.
982183, well, in an example I showed, with 105 24, with 106 24, etc.
But thanking Akina 24 times at it is not necessary wink

5

Re: Help to optimize UPDATE

f50 wrote:

with 105 24, with 106 24

WHERE...
AND idday IN (105, 106)

allows for once... And if it is necessary to process all idday - so generally  this condition from request.

6

Re: Help to optimize UPDATE

Akina , yes yes, precisely, already implemented, !!! Happiness and good luck to you!