--STOCKにない商品
WITH
wk_list1(ITEM_CODE)
AS
 (
 SELECT
  *
 FROM
  (SELECT ITEM_CODE FROM ITEM WHERE STOCK_MANAGEMENT_TYPE=1) a
 EXCEPT
 SELECT
  *
 FROM
  (SELECT ITEM_CODE FROM STOCK) b
 )
,
--商品毎の最終出荷日(LAST_DELIVERY_DATE)の最大値が6カ月以上前で実在庫数(ACTUAL_AMT)がゼロのデータ
wk_list2(ITEM_CODE)
AS
 (
 SELECT
  x.ITEM_CODE
 FROM
  ITEM x
  inner join
  (SELECT ITEM_CODE, MAX(LAST_DELIVERY_DATE) as LAST_DELIVERY_DATE, SUM(ACTUAL_AMT) as ACTUAL_AMT FROM STOCK GROUP BY ITEM_CODE) y
  on x.ITEM_CODE = y.ITEM_CODE
 WHERE
  x.STOCK_MANAGEMENT_TYPE = 1
  and y.LAST_DELIVERY_DATE < '2023-01-01'
  and y.ACTUAL_AMT = 0
 )
--商品を削除
DELETE
FROM
 ITEM
WHERE
 ITEM_CODE in
 (select ITEM_CODE from wk_list1 union select ITEM_CODE from wk_list2)