UPDATE
    ITEM
SET
    ITEM_POPULAR_RANK = 0
;

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