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
group by i.item_code
),
tmp2 as (
select i.*,rank() over (order by qn desc,odd desc, code desc) as rr from 
item i left 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;