WITH
A AS (
  SELECT ITEM_CODE, RANK() OVER (ORDER BY SUM(ORDER_QTY) DESC, MAX(ORDER_DATE) DESC, ITEM_CODE DESC) AS R
  FROM ITEM JOIN ORDERS_DTL USING (ITEM_CODE) JOIN ORDERS USING (ORDER_NO) WHERE ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30' GROUP BY ITEM_CODE
),
B AS (
  SELECT ITEM_CODE, CASE WHEN R IS NULL THEN 0 ELSE R END AS R
  FROM ITEM LEFT JOIN A USING (ITEM_CODE)
)
UPDATE ITEM SET ITEM_POPULAR_RANK = R FROM B WHERE ITEM.ITEM_CODE = B.ITEM_CODE