with BASE as (
select
ITEM_CODE,
sum(D.ORDER_QTY) QTY,
max(O.ORDER_DATE) ORDER_DATE
from
ORDERS_DTL D
inner join
ORDERS O
on
D.ORDER_NO = O.ORDER_NO
group by
ITEM_CODE
),R as (
select 
ITEM_CODE,
row_number() over (order by QTY desc, ORDER_DATE desc, ITEM_CODE desc) as RANK
from BASE
),RES as (
select
ITEM.ITEM_CODE,
case when R.RANK is null then 0 else R.RANK end as RANK
from
ITEM
left outer join
R
on
ITEM.ITEM_CODE = R.ITEM_CODE
)

update
ITEM
set
ITEM_POPULAR_RANK = RES.RANK
from
    RES
where
    ITEM.ITEM_CODE = RES.ITEM_CODE