テーブル内での従属関係を一括選択あるいは削除したい

Question

以下のような従属関係を表すテーブルにおいて、従属する配下の従属関係のあるレコードも選択、削除することは可能でしょうか。

上司='上田一郎'を指定した場合、行1,2,3,6を一括選択、削除したいのです。実際には複数のキーで実装する予定です。よろしくお願いします。

行 上司 部下
1 上田一郎 東田二郎
2 上田一郎 西田四郎
3 東田二郎 山田三郎
4 佐藤幸一 鈴木康二
5 佐藤幸一 井上幸三
6 山田三郎 北田四郎

Answer 1

利用されているRDBがわからないので一般的な答えですが、以下のページを参考に。

津島博士のパフォーマンス講座｜第31回 再帰的問合せについて

Comments

[SatoshiM]

回答ありがとうございます！

SQLServerです。
再帰CTEが使えそうとは思っていたのですが、今一つ確信が持てずにいました。こちらをじっくり読ませていただきます。

Answer 2

ひとつのDBのmysqlで簡単なストアドを作成してみました

#tabel定義
create table tableA (id int, joshi text, buka text);

#ストアド
DELIMITER //
CREATE PROCEDURE sample03 ( IN in_name TEXT )
BEGIN

DECLARE _done int;
DECLARE _joshi_name TEXT;
DECLARE _buka_name TEXT;
DECLARE cur CURSOR FOR
  SELECT joshi, buka FROM tableA WHERE joshi=in_name;
DECLARE EXIT HANDLER FOR NOT FOUND SET _done = 0;

SET _done = 1;
OPEN cur;
WHILE _done DO
  FETCH cur INTO _joshi_name, _buka_name;
  CALL sample03 (_buka_name);
  DELETE FROM tableA WHERE joshi=_joshi_name AND buka=_buka_name;
END WHILE;
CLOSE cur;

END
//
DELIMITER ;


#実行
set max_sp_recursion_depth=12;
CALL sample03 ('上田一郎');

postgresでも作成してみました。指定された上司のレコードから、上司部下の関係のテーブルを最深階層（部下がいない）までを読み、そこから逆に上に戻りながら削除を行う動きとなります。

#ストアド
CREATE OR REPLACE FUNCTION test_sp_func9(in_name TEXT)
RETURNS INT AS $$

DECLARE rc tableA%rowtype;
DECLARE _ret int;
BEGIN

    FOR rc IN SELECT * FROM tableA WHERE joshi=in_name LOOP
        SELECT test_sp_func9 (rc.buka) INTO _ret;
        DELETE FROM tableA WHERE joshi=rc.joshi AND buka=rc.buka;
    END LOOP;
    RETURN 0;

END;
$$ LANGUAGE plpgsql;

#実行
select test_sp_func9('上田一郎')；

SqlServerも作成しました。久々で自信はありませんが、動く様です。

WITH EMP_CTE(id, jyoshi, buka) 
AS
(
    SELECT 
        id, 
		jyoshi,
        buka
    FROM 
        testAA 
    WHERE jyoshi = '上田一郎' 

    UNION ALL

    SELECT 
        testAA.id, 
        testAA.jyoshi,
        testAA.buka
    FROM 
        testAA 
            INNER JOIN EMP_CTE 
            ON testAA.jyoshi = EMP_CTE.buka
)
-- 
DELETE FROM testAA WHERE id in (SELECT id FROM EMP_CTE);

Comments

[SatoshiM]

回答ありがとうございます！

mysqlは環境が無くて試せていません。

ざっと、拝見するとこれでは1,2は削除できても、配下の3,6は削除できないのではないでしょうか？

[A.Ichi]

リカーシブル（再帰）により削除されると思います。一応テストはしました。DBは何をお持ちでしょうか？

[SatoshiM]

postgresでもありがとうございます。恐縮です。

DBはMS-SQLServerです。ストアドで再帰できるんですね！

[SatoshiM]

A.Ichoさん、SQLServerでも回答いただき、誠にありがとうございました。

回答を参考に、id無しで以下の様に改造して、動作確認しました！

```SQL
WITH 上司部下_CTE(上司, 部下)
AS
(
SELECT
a.上司,
a.部下
FROM
上司部下 a
WHERE a.上司 = '上田一郎'

UNION ALL

SELECT
b.上司,
b.部下
FROM
上司部下 b INNER JOIN 上司部下_CTE c
ON b.上司 = c.部下
)

DELETE FROM 上司部下 WHERE 上司 in (SELECT 上司 FROM 上司部下_CTE);
```