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