with stock_sum as (
select item_code,sum(ACTUAL_AMT) as ACTUAL_AMT ,MAX(ifnull(LAST_DELIVERY_DATE,'2023-06-01')) as LAST_DELIVERY_DATE
from STOCK
group by item_code
),
need_item as (
select item_code,ACTUAL_AMT,LAST_DELIVERY_DATE
from stock_sum
where 
LAST_DELIVERY_DATE >= date('2023-06-01', '-6 months') 
or ( LAST_DELIVERY_DATE <= date('2023-06-01', '-6 months') and ACTUAL_AMT != 0)
or LAST_DELIVERY_DATE is null
 )
-- select * from need_item
delete from item as i
where not EXISTS (
select * from need_item as sa
where sa.item_code = i.item_code)
and i.STOCK_MANAGEMENT_TYPE = 1