update ITEM
set ITEM_POPULAR_RANK = 0;
-----------------------------------
with sub1 as(
	select
		od.ITEM_CODE
		,SUM(od.ORDER_QTY) as SUM_QTY
		,MAX(om.ORDER_DATE) as MAX_ORDER_DATE
	from ORDERS om
		inner join ORDERS_DTL od
		on  om.ORDER_NO = od.ORDER_NO
	where
		om.ORDER_DATE between '2023-04-01' and '2023-06-30'
	group by
		od.ITEM_CODE 
)
,sub2 as (
	select
		ITEM_CODE
		,row_number() over (order by SUM_QTY desc, MAX_ORDER_DATE desc, ITEM_CODE desc) as rank
	from sub1
)
update ITEM as it
set ITEM_POPULAR_RANK = (select rank from sub2 s where it.ITEM_CODE = s.ITEM_CODE)
where it.ITEM_CODE in (select ITEM_CODE from sub2 s where it.ITEM_CODE = s.ITEM_CODE);
-----------------------------------
select * from ITEM;
-----------------------------------