WITH MAX_LAST(ITEM_CODE,LAST_DELIVERY_DATE,ACTUAL_AMT) AS(
SELECT ITEM_CODE,MAX(LAST_DELIVERY_DATE),ACTUAL_AMT
FROM STOCK
GROUP BY ITEM_CODE
)
DELETE FROM ITEM
WHERE (ITEM_CODE NOT IN (SELECT STOCK.ITEM_CODE FROM STOCK INNER JOIN ITEM ON STOCK.ITEM_CODE=ITEM.ITEM_CODE)
OR ((SELECT ifnull(LAST_DELIVERY_DATE,'2023-06-01') FROM MAX_LAST INNER JOIN ITEM ON MAX_LAST.ITEM_CODE = ITEM.ITEM_CODE) <= '2022-12-01'
AND (SELECT ACTUAL_AMT FROM MAX_LAST INNER JOIN ITEM ON MAX_LAST.ITEM_CODE = ITEM.ITEM_CODE) = 0
))
AND STOCK_MANAGEMENT_TYPE = 1