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