UPDATE
    ITEM
SET
    ITEM_POPULAR_RANK = ITEM_RANK.POPULAR_RANK
FROM (
    SELECT
        ITEM_CODE,
        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'
        ) AS O
        INNER JOIN
            ORDERS_DTL USING (ORDER_NO)
        GROUP BY ITEM_CODE
        
        UNION
        
        SELECT
            ITEM_CODE,
            0 AS POPULAR_RANK
        FROM
            ITEM
        WHERE
            NOT EXISTS (
                SELECT
                    1
                FROM
                    ORDERS
                INNER JOIN
                    ORDERS_DTL USING(ORDER_NO)
                WHERE
                    ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
                    AND ITEM.ITEM_CODE = ORDERS_DTL.ITEM_CODE
            )
    )
) AS ITEM_RANK
WHERE
    ITEM.ITEM_CODE = ITEM_RANK.ITEM_CODE
;