Question

I need to update below table so that value in column 3 vould be equal to max value +1 (for each A where B = 339)

            A  |        B    |   C  
         14430 |         339 |    -1     (value c should = 3)
         14430 |          80 |     0
         14430 |         262 |     1
         14430 |          39 |     2

         14431 |         339 |    -1     (value c should = 1)           
         14431 |          41 |     0

         14432 |         339 |    -1     (value c should = 0)   

         14433 |         284 |     0 
         14452 |         298 |     0
         14452 |         181 |     1

         14490 |         339 |    -1     (value c should = 0)   

         14491 |         339 |    -1     (value c should = 0) 

         14492 |         339 |    -1     (value c should = 0)

Answer 1

UPDATE tbl
SET    c = x.new_c
FROM (
    SELECT a, max(c) + 1 AS new_c
    FROM   tbl
    -- WHERE  b <> 339  -- exclude b = 339 from calculating the maximum?
    GROUP  BY a
    ) x
WHERE  tbl.b = 339
AND    tbl.a = x.a
AND    tbl.c IS DISTINCT FROM x.new_c  -- to avoid pointless updates

友情链接