UPDATE
    ITEM
SET
     ITEM_POPULAR_RANK = 0;
WITH subq AS (
    SELECT
        OD.ITEM_CODE AS ITEM
        ,SUM(OD.ORDER_QTY) AS AMT
        ,MAX(O.ORDER_DATE) AS DATE
        ,ROW_NUMBER() OVER (ORDER BY SUM(OD.ORDER_QTY) DESC, MAX(O.ORDER_DATE) DESC, OD.ITEM_CODE DESC) AS RANK
    FROM
        ORDERS AS O
    LEFT OUTER JOIN
        ORDERS_DTL AS OD
      ON
        O.ORDER_NO = OD.ORDER_NO
    WHERE
        ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY
        OD.ITEM_CODE
    )
/*
SELECT
    ITEM.ITEM_CODE
    ,ITEM_POPULAR_RANK
    ,subq.RANK
FROM
    ITEM
LEFT OUTER JOIN
    subq
  ON
    ITEM.ITEM_CODE = subq.ITEM;
*/
UPDATE
    ITEM
SET
    ITEM_POPULAR_RANK = subq.RANK
FROM
    subq
WHERE
    ITEM.ITEM_CODE = subq.ITEM;