さらに、cid を間違えないようにしました。

まず、これで spec.cbyochu テーブルを作成します。cid は自由に付け直しても大丈夫です。

--/d
drop table if exists spec.cbyochu;
create table spec.cbyochu (cid integer primary key, cname varchar unique);
begin transaction;
insert into spec.cbyochu values(1, '病害');
insert into spec.cbyochu values(2, '害虫');
insert into spec.cbyochu values(3, '雑草');
insert into spec.cbyochu values(4, '鳥獣');
insert into spec.cbyochu values(5, 'その他');
commit;

次に、これで m_byochu テーブルを作成します。

--/d
drop table if exists t_byochu;
create temp table t_byochu as select distinct byochu from m_tekiyo order by byochu;
drop table if exists m_byochu;
create table m_byochu (idbyochu integer primary key autoincrement, byochu varchar unique, cid integer);
begin transaction;
insert into m_byochu (byochu, cid) select byochu, (select cid from spec.cbyochu where cname = 'その他') as cid from t_byochu where ifnullstr(byochu, '-') != '-';
--分類処理
update m_byochu set cid = (select cid from spec.cbyochu where cname = '鳥獣') where byochu in (select distinct byochu from m_tekiyo where mokuteki = '忌避' or bango in (select bango from m_kihon where koka like '%忌避%'));
update m_byochu set cid = (select cid from spec.cbyochu where cname = '害虫') where byochu in (select distinct byochu from m_tekiyo where mokuteki in ('誘引', '交尾阻害') or bango in (select bango from m_kihon where yoto like '殺虫%' or koka like '%誘引' or koka like '%殺虫%'));
update m_byochu set cid = (select cid from spec.cbyochu where cname = '雑草') where byochu like '%雑草%' or byochu in (select distinct byochu from m_tekiyo where bango in (select bango from m_kihon where yoto = '除草剤'));
update m_byochu set cid = (select cid from spec.cbyochu where cname = '病害') where byochu like '%病%' or byochu like'%症%' or byochu like '%菌%' or byochu like '%腐%' or byochu like '%タケ';
update m_byochu set cid = (select cid from spec.cbyochu where cname = 'その他') where byochu like '%活性化%' or byochu like '%覚醒%';
--分類例外処理
update m_byochu set cid = (select cid from spec.cbyochu where cname = '害虫') where byochu like '%ナメクジ' or byochu like '%ハダニ%';
update m_byochu set cid = (select cid from spec.cbyochu where cname = '鳥獣') where byochu in ('イノシシ', 'カモシカ', 'ニホンジカ', 'モグラ', '野ウサギ', '野ソ', '害獣');
commit;