Team,
need to UPDATE exist table column based on the other column of same table with some logic.

Source table as follows:

PRICELIST

BU_NAME VAL_STATUS  LOOPSETID
AAA             Y   0
BBB             Y   0
CCC             N   0
DDD             Y   0
EEE             Y   0
FFF             Y   0
GGG             Y   0

Need to pick only VAL_STATUS='Y‘ records and and sort them as max 2 records per set and update column LOOPSETID as follows:

BU_NAME VAL_STATUS  LOOPSETID
AAA             Y   1
BBB             Y   1
CCC             N   0
DDD             Y   2
EEE             Y   2
FFF             Y   3
GGG             Y   3

I have the below query from for that particular column value
trunc((rowno + p_max_batch_rec – 1) / p_max_batch_rec) loopsetid

Where p_max_batch_rec is parameter that currently set to 2 for this case. The query is used with insert in other target table from source table column comparison.

But now I need to update this value in my source table itself. So need merge statement.