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