[Q&A] 【SQL】別テーブルに情報を加工して移行する

【SQL】別テーブルに情報を加工して移行する

解決したいこと

現在SQLの学習のため、大量のデータをテーブルに加工して移行することをしています。
以下のように情報を別テーブルに移行する際にどのようなSQLを書けばいいかわからず、困っております。

やりたいこと

以下の画像のようにoroginalテーブルから、categoryテーブルにデータを移行したい。
oroginalテーブルのcategoryカラムの情報を"/"で区切り1行ずつcategoryテーブルに挿入する。

自分で試したこと

そもそもこのやり方であっているのかわかりませんが、、、
SUBSTRING_INDEXを使用して"/"毎に文字列を切り出すことはできましたが、
insertする際にwhere句での条件を指定がうまくできず質問することに至りました。
どなたかお力を貸していただけると助かります。

INSERT INTO category(parent_id, category, name_all) 
VALUES(NULL, (SELECT SUBSTRING_INDEX((SELECT category from original where id = ？？？), '/', 1)), NULL

1Answer

@YottyPG posted at 2022-02-02

あまり見慣れない分割の仕方をやっていたので気になってクエリを書いてみました。
特定の文字で分割して縦に並べる方法は、こちらの質問が参考になりました。

分割の方法ですが。「/」が2つあれば3行、1つあれば2行用意する必要があります。
「numbers」というテーブルを用意して、そのテーブルをいい感じにJOINすることで
「/」の数+1行用意しています。

次に「SUBSTRING_INDEX」を活用して、「/」で分割した単語を縦に並べた形で取得します。

あとは希望通りの形に出力できるように、case文なりでいい感じに出力しています。


CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3);

with main as
(
  select *
  from original
  inner join numbers
  on CHAR_LENGTH(category) - CHAR_LENGTH(REPLACE(category, '/', '')) >= n-1 
  order by id, n
 )
select 
	ROW_NUMBER() OVER (ORDER BY id)
   ,case when n=1 then null 
         else ROW_NUMBER() OVER (ORDER BY id) - 1 
    end
   ,SUBSTRING_INDEX(SUBSTRING_INDEX(category, '/', n), '/', -1)
   ,case when (select max(n) from main as sub where sub.id = main.id) = n then category
         else null
    end
from main

こちらでクエリの動作確認もできます。

今回はカテゴリが1行に3つまで前提で作成していますが、それよりも多くなれば「numbers」の数を増やす必要があります。
また、カテゴリの数が予想できず、かなり大きくなる可能性がある場合は、while文なりを利用してループして分割していくしかないように思います。

またWith句は「name_all」を出すために利用しましたが、もうちょっとうまくやればWith句なしでもかけそうな気がします。

思ったよりも複雑になってしまったので、クエリ見てわからないことなどあればお申し付けください

2Like

Are you sure you want to delete the question?