UPDATE ITEM
SET ITEM_POPULAR_RANK = Z.RANK
FROM (
	SELECT B.ITEM_CODE,RANK() OVER(ORDER BY COUNT(B.ITEM_CODE) DESC,E.MAX_DATE DESC,B.ITEM_CODE DESC) RANK
	FROM ORDERS A
		,ORDERS_DTL B
		,(SELECT D.ITEM_CODE,MAX(C.ORDER_DATE) MAX_DATE FROM ORDERS C ,ORDERS_DTL D WHERE C.ORDER_NO = D.ORDER_NO AND C.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30' GROUP BY D.ITEM_CODE) E
	WHERE A.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
	AND A.ORDER_NO = B.ORDER_NO
	AND B.ITEM_CODE = E.ITEM_CODE
	GROUP BY B.ITEM_CODE
) Z
WHERE ITEM.ITEM_CODE = Z.ITEM_CODE