UPDATE ITEM
    SET ITEM_POPULAR_RANK = 0;
WITH ITEM_RANK AS (
    SELECT
        ordd.ITEM_CODE,
        SUM(ordd.ORDER_QTY) AS TOTAL_COUNT
        ,MAX(ord.ORDER_DATE) AS LAST_ORDER
        ,RANK() OVER(ORDER BY SUM(ordd.ORDER_QTY) DESC, MAX(ord.ORDER_DATE) DESC, ordd.ITEM_CODE DESC) AS RANKING
    FROM
        ORDERS_DTL ordd
    INNER JOIN ORDERS ord
        ON ordd.ORDER_NO = ord.ORDER_NO
    WHERE
        ord.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY
        ordd.ITEM_CODE
    ORDER BY
        TOTAL_COUNT DESC
)
UPDATE ITEM
    SET
        ITEM_POPULAR_RANK = TMP.RANKING
    FROM (
        SELECT
            ITEM_CODE
            ,RANKING
        FROM
            ITEM_RANK
    ) AS TMP
    WHERE
        ITEM.ITEM_CODE = TMP.ITEM_CODE;