UPDATE ITEM
SET
 ITEM_POPULAR_RANK = 0;

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