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

## 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 🙂