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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply