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

by

The 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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment