update item 
set item_popular_rank=
    case 
        when item.item_code=temp.item_code 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 
where item.item_code=temp.item_code;