DELETE FROM
    ITEM
WHERE
    ITEM.STOCK_MANAGEMENT_TYPE = 1 AND
    (
        -- 在庫テーブル(STOCK)に商品コード(ITEM_CODE)が存在しない
        NOT EXISTS(
            SELECT
                STOCK.ITEM_CODE
            FROM
                STOCK
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE
        )
        OR
        -- 商品毎の最終出荷日(LAST_DELIVERY_DATE)の最大値が6カ月以上前で実在庫数(ACTUAL_AMT)がゼロ
        (
            SELECT
                CASE 
                    WHEN SUM(STOCK.ACTUAL_AMT) = 0 AND MAX(STOCK.LAST_DELIVERY_DATE) <= DATE('2023-06-01', '-6 months') THEN TRUE
                    ELSE FALSE
                END AS RES
            FROM
                STOCK
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE AND
                (
                    STOCK.LAST_DELIVERY_DATE IS NULL OR
                    STOCK.LAST_DELIVERY_DATE <= DATE('2023-06-01', '-6 months')
                )
        ) = TRUE
    )
;