update item 
set item_popular_rank=
    case 
        when temp.item_code is not null then temp.setrank 
        else 0 
    end 
from    
(select item_code,sum(b.ORDER_QTY) as item_total, max(a.order_date) as max_date ,RANK() OVER(ORDER BY sum(b.ORDER_QTY) desc,max(a.order_date) desc,b.item_code desc) as setrank from orders as A 
inner join orders_dtl as B
on a.order_no=b.order_no 
where a.order_date between '2023-04-01' and '2023-06-30' 
group by item_code 
) as temp ;


update item 
    set item_popular_rank=a.setrank 
from 
(
select item.item_code ,
    case when tmp.item_code is not null then rank 
    else 0 end as setrank 
from item 
left outer join
(
select 
    odtl.item_code,sum(order_qty) as total ,max(od.order_date) ,rank() over(order by sum(order_qty) desc,max(od.order_date) desc,odtl.item_code desc) as rank  
from orders_dtl as ODtl 
inner join orders as OD 
on odtl.order_no=od.order_no 
where od.order_date between '2023-04-01' and '2023-06-30' 
group by odtl.item_code ) as tmp 
on item.item_code=tmp.item_code) as a 
where item.item_code = a.item_code;