with tmp as (
SELECT
sum(order_qty) as qn,
i.item_name as name,
i.item_code as code,
max(order_date) odd
from item i left join  orders_dtl od on od.item_code=i.item_code
left join orders o on o.order_no=od.order_no
where order_date >= '2023-04-01' and  order_date <= '2023-06-30' 
group by i.item_code
),
tmp2 as (
select i.*,rank() over (order by qn desc,odd desc, code desc) as rr from 
item i inner join tmp on i.item_code = tmp.code
)
update item
set ITEM_POPULAR_RANK =
case when rr is null then 0
ELSE rr
end
from tmp2 where item.item_code=tmp2.item_code;

with tmp as (
SELECT
sum(order_qty) as qn,
i.item_name as name,
i.item_code as code,
max(order_date) odd
from item i left join  orders_dtl od on od.item_code=i.item_code
left join orders o on o.order_no=od.order_no
where order_date >= '2023-04-01' and  order_date <= '2023-06-30' 
group by i.item_code
),
tmp2 as (
select i.*,rank() over (order by qn desc,odd desc, code desc) as rr from 
item i inner join tmp on i.item_code = tmp.code
)
update item 
set ITEM_POPULAR_RANK=0
where not exists (select 1 from tmp2 where item.item_code = tmp2.item_code);