多対多リレーションを修正するストアドプロシージャ

下の画像の右側と左側が多対多のテーブルのレコードで、真ん中の薬のカプセルみたいなやつが、中間テーブルのレコードとします。

具体的にいうと、左側の「LIZ」、「LITA」はアーティストで、右側の「KING」というのは楽曲です。

「KING」という２つのレコードは同じものを意図しているのにもかかわらず、IDが異なるので違うものとして扱われてしまっています。

なのでMAINを残し、SUBを消すために、以下のように中間テーブルをUPDATEしたのち、いらなくなったSUBレコードを削除します。

これをするSQLは以下のようになります。

-- 左側のテーブル : artists
-- 中間テーブル : music_artists
-- 右側のテーブル : music
update music_artists set music_id = {main} where music_id = {sub};
delete from music where id = sub;

これでうまくいきそうですが、以下のような場合にうまくいきません。
UPDATEすると複合主キーの主キー制約に反するので、UPDATEできないのです。

上記のパターンが

• UPDATE → DELETE

のパターンが必要であるのに対し、今回のパターンでは

• DELETE → DELETE

する必要があります。

これを振り分けるためには、ストアドプロシージャでカーソル操作する必要があります。
MySQLです。

CREATE PROCEDURE `join_music`(main INT, sub INT)
BEGIN
  DECLARE current_artist_id INT;
  
  DECLARE done INT DEFAULT FALSE;
  
  DECLARE cur CURSOR FOR SELECT artist_id FROM music_artists WHERE music_id = sub;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;


  cur_loop: LOOP
    IF done THEN
      LEAVE cur_loop;
    END IF;
    
    FETCH cur INTO current_artist_id;
    
    IF EXISTS (SELECT * FROM music_artists WHERE artist_id = current_artist_id AND music_id = main) THEN
		DELETE FROM music_artists WHERE artist_id = current_artist_id AND music_id = sub;
	ELSE
		UPDATE music_artists SET music_id = main WHERE artist_id = current_artist_id AND music_id = sub;
    END IF;
  END LOOP;
  
  CLOSE cur;

  DELETE FROM music WHERE id = sub;
END

ちょっとパターンを考慮するだけでこんな複雑になるんですね。