UPDATE item
SET ITEM_POPULAR_RANK=0;
WITH t1 AS (SELECT D.ITEM_CODE
                 , MAX(ORDER_DATE) DT
                 , SUM(ORDER_QTY) qty
            FROM ORDERS O
                 JOIN ORDERS_DTL D ON D.ORDER_NO = O.ORDER_NO
            WHERE O.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
            GROUP BY D.ITEM_CODE)
   , t2 AS (SELECT t1.*
                 , RANK() OVER (ORDER BY qty DESC, dt DESC,ITEM_CODE DESC
        ) rnk
            FROM t1)
UPDATE item
SET ITEM_POPULAR_RANK = (SELECT t2.rnk FROM t2 WHERE t2.ITEM_CODE = item.ITEM_CODE)
WHERE item.ITEM_CODE IN (SELECT t2.ITEM_CODE FROM t2);