Query to update one column based on the amount of another column

Question

I have a table with below structure :

create table Z_TEST_ONE
(
  col_id   NUMBER,
  bed_amnt NUMBER,
  bes_amnt NUMBER,
  bop      NUMBER
)

Sample data is like this :

     col_id    bed_amnt    bes_amnt    bop
   ---------- ----------- ---------- -------
       1        1000         0         20
       1        5000         0         7
       1          0         3000       10
       1          0          6         14
       2        1000         0         1
       2        2000         0         2
       2          0         1000       3
       2          0         2000       4

Now what I need is this : For each col_id , bop column for highest bes_amnt must be equal to the bop for highest bed_amnt. So after executing the query the result must look like this :

     col_id    bed_amnt    bes_amnt    bop
   ---------- ----------- ---------- -------
       1        1000         0         20
       1        5000         0         7
       1          0         3000       7 ---> changed from 10 to 7
       1          0          6         14
       2        1000         0         1
       2        2000         0         2
       2          0         1000       3
       2          0         2000       2 ---> changed from 4 to 2

I wrote a query for this as you can see below :

update z_test_one a
   set a.bop =
       (select t.bop /* bop for highest bed_amnt */
          from z_test_one t
         where t.bed_amnt = (select max(bed_amnt)
                               from z_test_one
                              where col_id = a.col_id
                              group by col_id))     
 where a.bes_amnt = (select max(bes_amnt)
                       from z_test_one
                      where col_id = a.col_id
                      group by col_id)

this query works fine and gives me the correct result , I was wondering if there are better ways to write the desired query.

Thanks in advance

Answer 1

I wonder if finding the new value might be better with:

update z_test_one a
  set a.bop =
      (select      t.bop
          from     z_test_one t
        where      t.col_id = a.col_id
          order by t.bed_amnt desc
          limit    1)     
where a.bes_amnt = (select max(bes_amnt)
                      from z_test_one
                      where col_id = a.col_id
                      group by col_id)

Perhaps the where clause might be better with:

where (col_id, bes_amnt) in (select col_id, max(bes_amnt)
                      from z_test_one
                      group by col_id)

I would try them with your data volumes, stats etc and see how the actual plans and execution compare.