UPDATE ITEM
SET
    ITEM_POPULAR_RANK = 0;

WITH
    SUM AS (
        SELECT
            od.ITEM_CODE,
            SUM(od.ORDER_QTY) AS SUM_QTY,
            MAX(o.ORDER_DATE) AS MAX_DATE
        FROM
            ORDERS as o
            INNER JOIN ORDERS_DTL AS od ON o.ORDER_NO = od.ORDER_NO
        WHERE
            o.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
        GROUP BY
            od.ITEM_CODE
    ),
    RANK AS (
        SELECT
            ITEM_CODE,
            ROW_NUMBER() OVER (
                ORDER BY
                    SUM_QTY DESC,
                    MAX_DATE DESC,
                    ITEM_CODE DESC
            ) AS POPULAR_RANK
        FROM
            SUM
    )
UPDATE ITEM
SET
    ITEM_POPULAR_RANK = RANK.POPULAR_RANK
FROM
    RANK
WHERE
    ITEM.ITEM_CODE = RANK.ITEM_CODE;