WITH STC_LDD AS (
    SELECT
        ITEM_CODE
        , MAX(LAST_DELIVERY_DATE) LATEST_LDD
    FROM
        STOCK
    WHERE
        1=1
        AND LAST_DELIVERY_DATE IS NOT NULL
    GROUP BY
        ITEM_CODE
), WHERE_COND AS (
    SELECT
        a.ITEM_CODE
    FROM 
        ITEM a
        LEFT JOIN STOCK b
            ON a.ITEM_CODE = b.ITEM_CODE
        LEFT JOIN STC_LDD c
            ON a.ITEM_CODE = c.ITEM_CODE
    WHERE
        1=1
        AND STOCK_MANAGEMENT_TYPE = 1     -- 1:在庫管理する
        AND (
            b.ITEM_CODE IS NULL
            OR (
                b.ITEM_CODE IS NOT NULL
                AND c.LATEST_LDD <= datetime('2023-06-01', '-6 months') 
            )
        )
)
DELETE
FROM 
    ITEM
WHERE
    ITEM.ITEM_CODE IN (
        SELECT ITEM_CODE FROM WHERE_COND
    )
;