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



  • 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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2