UPDATE ITEM 
SET
    ITEM_POPULAR_RANK = 0; 

WITH SUB as ( 
    select
        odd.ITEM_CODE
        , SUM(ORDER_QTY)
        , MAX(ORDER_DATE)
        , ROW_NUMBER() OVER ( 
            ORDER BY
                SUM(ORDER_QTY) desc
                , MAX(ORDER_DATE) desc
                , ITEM_CODE DESC
        ) as RANK 
    from
        ORDERS as od 
        inner join ORDERS_DTL as odd 
            on od.ORDER_NO = odd.ORDER_NO 
    where
        ORDER_DATE between '2023-04-01' and '2023-06-30' 
    group by
        odd.ITEM_CODE
) UPDATE ITEM 
SET
    ITEM_POPULAR_RANK = RIW.RANK 
FROM
    (select ITEM_CODE, RANK from sub) AS RIW 
where
    ITEM.ITEM_CODE = RIW.ITEM_CODE