Merge/Insert data into exist table column based on conditions using SQL or PLSQL from Procedure call

Question

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.

When I execute following query facing issue

MERGE INTO PRICELIST_SRC_TBL pt 
USING PRICELIST_SRC_TBL ps 
ON    (ps.VAL_STATUS = 'Y') 
WHEN matched THEN UPDATE 
SET pt.LOOPSETID = select trunc((rowno + 2 - 1) / 2) loopsetid from
(select ROW_NUMBER() OVER( ORDER BY val_status ) rowno from PRICELIST_SRC_TBL where VAL_STATUS = 'Y');

the error I get is as follows

Error starting at line : 1 in command -
MERGE INTO PRICELIST_SRC_TBL pt 
USING PRICELIST_SRC_TBL ps 
ON    (ps.VAL_STATUS = 'Y') 
WHEN matched THEN UPDATE 
SET pt.LOOPSETID = select trunc((rowno + 2 - 1) / 2) loopsetid from
(select ROW_NUMBER() OVER( ORDER BY val_status ) rowno from PRICELIST_SRC_TBL where VAL_STATUS = 'Y')
Error at Command Line : 5 Column : 20
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:

Answer 1

Put that query in the "USING" clause, not in the merge_update_clause. Join source and target on the row identifier. Putting that together gives:

MERGE INTO pricelist_src_tbl pt
USING (
   SELECT bu_name
         ,trunc((rowno + 2 - 1) / 2) loopsetid
     FROM ( SELECT bu_name
                  ,ROW_NUMBER()
                    OVER(
                     ORDER BY val_status
                    ) rowno
                   FROM pricelist_src_tbl
           WHERE val_status = 'Y'
          )
      ) ps ON ( ps.bu_name = pt.bu_name )
WHEN MATCHED THEN UPDATE
SET pt.loopsetid = ps.loopsetid;