--/d
-- テンポラリ効果テーブル
drop table if exists t_koka;
create temp table t_koka as select
  bango,
  (select kokaid from spec.bunrui where seibun1 = seibun) as koka1,
  (select kokaid from spec.bunrui where seibun2 = seibun) as koka2,
  (select kokaid from spec.bunrui where seibun3 = seibun) as koka3,
  (select kokaid from spec.bunrui where seibun4 = seibun) as koka4,
  (select kokaid from spec.bunrui where seibun5 = seibun) as koka5
from m_kihon;
alter table t_koka add column idkoka integer;
alter table t_koka add column koka varchar;
update t_koka set idkoka = ifnull(koka1, 0) | ifnull(koka2, 0) | ifnull(koka3, 0) | ifnull(koka4, 0) | ifnull(koka5, 0);
update t_koka set idkoka = (select kokaid from spec.koka where koka = '展着') where idkoka = 0;
update t_koka set koka = concat('・',
  (select koka from spec.koka where kokaid = idkoka & 3),
  (select koka from spec.koka where kokaid = idkoka & 4),
  (select koka from spec.koka where kokaid = idkoka & 8),
  (select koka from spec.koka where kokaid = idkoka & 16),
  (select koka from spec.koka where kokaid = idkoka & 32),
  (select koka from spec.koka where kokaid = idkoka & 64),
  (select koka from spec.koka where kokaid = idkoka & 128),
  (select koka from spec.koka where kokaid = idkoka & 256),
  (select koka from spec.koka where kokaid = idkoka & 512),
  (select koka from spec.koka where kokaid = idkoka & 1024),
  (select koka from spec.koka where kokaid = idkoka & 2048),
  (select koka from spec.koka where kokaid = idkoka & 4096),
  (select koka from spec.koka where kokaid = idkoka & 8192),
  (select koka from spec.koka where kokaid = idkoka & 16384),
  (select koka from spec.koka where kokaid = idkoka & 32768)
);
-- m_kihon 更新
update m_kihon set koka = (select koka from t_koka where bango = m_kihon.bango);
drop table if exists t_koka;