さらに、alias フィールドを追加して、既知の害虫グループについては別名として害虫グループ名を入れるようにしました。ただし、害虫グループの分類がちゃんとできているかどうかは検証していません。
害虫グループの登録に関しては、思ったより時間がかかりませんでした。

なお、害虫名では「イネアオムシ」、「ワタヘリクロノメイガ」を別名として追加しています。病害名では、晩腐病のみ別名を登録しています。雑草の地方名なども別名に追加すると良いかもしれません。

--/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, alias varchar);
begin transaction;
insert into m_byochu (byochu, cid) select byochu, 0 from t_byochu where ifnullstr(byochu, '-') != '-';
--分類処理
update m_byochu set cid = 4 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 = 3 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 = 1 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 = 2 where byochu like '%病%' or byochu like'%症%' or byochu like '%菌%' or byochu like '%腐%' or byochu like '%タケ';
update m_byochu set cid = 0 where byochu like '活性化%' or byochu like '%覚醒%';
--分類例外処理
update m_byochu set cid = 3 where byochu like '%ナメクジ';
update m_byochu set cid = 4 where byochu in ('イノシシ', 'カモシカ', 'ニホンジカ', 'モグラ', '野ウサギ', '野ソ', '害獣');
--害虫Ｇ優先処理1
update m_byochu set alias = 'アリＧ' where cid = 3 and byochu like '%アリ%';
update m_byochu set alias = 'ケラＧ' where cid = 3 and byochu like '%ケラ%';
update m_byochu set alias = 'コガＧ' where cid = 3 and byochu like '%コガ%';
update m_byochu set alias = 'セミＧ' where cid = 3 and byochu like '%ゼミ%';
update m_byochu set alias = 'ダニＧ' where cid = 3 and byochu like '%ダニ%';
update m_byochu set alias = 'ヤガＧ' where cid = 3 and byochu like '%ヤガ%';
--害虫Ｇ優先処理2
update m_byochu set alias = 'スズメガＧ' where cid = 3 and byochu like '%スズメ%';
update m_byochu set alias = 'アゲハＧ' where byochu like '%アゲハ%';
update m_byochu set alias = 'ケムシＧ' where byochu like '%ケムシ%';
update m_byochu set alias = 'イラガＧ' where byochu like '%イラガ%';
update m_byochu set alias = 'ウスバＧ' where byochu like '%ウスバ%';
update m_byochu set alias = 'ウンカＧ' where byochu like '%ウンカ%';
update m_byochu set alias = 'カレハガＧ' where byochu like '%カレハ%';
update m_byochu set alias = 'キバチＧ' where byochu like '%キバチ%';
update m_byochu set alias = 'シジミチョウＧ' where byochu like '%シジミ%';
update m_byochu set alias = 'シャクガＧ' where byochu like '%シャク%';
update m_byochu set alias = 'チビガＧ' where byochu like '%チビガ%';
update m_byochu set alias = 'トラガＧ' where byochu like '%トラガ%';
update m_byochu set alias = 'トリバガＧ' where byochu like '%トリバ%';
update m_byochu set alias = 'ネダニＧ' where byochu like '%ネダニ%';
update m_byochu set alias = 'ハダニＧ' where byochu like '%ハダニ%';
update m_byochu set alias = 'ハバチＧ' where byochu like '%ハバチ%';
update m_byochu set alias = 'ハムシＧ' where byochu like '%ハムシ%';
update m_byochu set alias = 'ヒトリガＧ' where byochu like '%ヒトリ%';
update m_byochu set alias = 'ミバエＧ' where byochu like '%ミバエ%';
update m_byochu set alias = 'ヨトウＧ' where byochu like '%ヨトウ%';
update m_byochu set alias = 'イナゴ・バッタＧ' where byochu like '%イナゴ%' or byochu like '%バッタ%';
update m_byochu set alias = 'ウワバＧ' where byochu like '%ウワバ%' or byochu like '%シタバ%' or byochu like '%キノカワガ' or byochu like '%ツメクサガ';
update m_byochu set alias = 'コガネムシ・ブイブイＧ' where byochu like '%コガネ%' or byochu like '%ドウガネ%' or byochu like 'ハナムグリ' or byochu like '%ブイブイ%';
update m_byochu set alias = 'コナガ・スガＧ' where byochu like '%コナガ%' or byochu like '%スムシ%' ;
update m_byochu set alias = 'ミノガ・ミノムシＧ' where byochu like '%ミノガ%' or byochu like '%ミノムシ%' ;
update m_byochu set alias = 'ツトガＧ' where byochu like '%ツトガ%' or byochu like '%ツトムシ%';
update m_byochu set alias = 'メイガＧ' where byochu like '%メイガ%' or byochu like '%メイチュウ%' or byochu like '%ツヅリガ%';
update m_byochu set alias = 'ホソガ・カワモグリＧ' where byochu like '%ホソガ%' or byochu like '%カワモグリ%'; -- ハマキガより前に実行
update m_byochu set alias = 'シンクイガ・シンクイムシＧ' where byochu like '%シンクイ%'; -- キクイムシより前に実行
update m_byochu set alias = 'ハマキガＧ' where byochu like '%ハマキ%' or byochu like '%サヤムシ%' or byochu like '%カワモグリガ%'; -- ハマキモドキより前に実行
update m_byochu set alias = 'マイマイ・カタツムリＧ' where byochu like '%マイマイ%' or byochu like '%カタツムリ%'; -- マイマイガより前に実行
update m_byochu set alias = 'マイマイ・ドクガＧ' where byochu like '%ドクガ%' or byochu like '%マイマイガ%';
--別名優先処理3
update m_byochu set alias = 'カメムシＧ' where byochu like '%カメムシ%'; -- ナガカメムシより前に実行
update m_byochu set alias = 'ゾウムシＧ' where byochu like '%ゾウムシ%'; -- チョッキリより前に実行
update m_byochu set alias = 'テントウムシＧ' where byochu like '%テントウ%'; -- テントウムシダマシより前に実行
update m_byochu set alias = 'ハモグリガＧ' where byochu like '%ハモグリ%'; -- ハモグリバエより前に実行
update m_byochu set alias = 'アブラムシＧ' where byochu like '%アブラムシ%' or byochu like '%ワタムシ%'; -- ネアブラムシ、タマワタムシより前に実行
update m_byochu set alias = 'カイガラムシＧ' where byochu like '%カイガラムシ%'; -- コナカイガラムシ、ロウカイガラムシより前に実行
--害虫Ｇ通常処理
update m_byochu set alias = 'アオムシＧ' where byochu like '%アオムシ%';
update m_byochu set alias = 'ジャガイモガ・イモコガＧ' where byochu like '%イモコガ%' or byochu like '%ジャガイモガ%';
update m_byochu set alias = 'ガガンボＧ' where byochu like '%ガガンボ%';
update m_byochu set alias = 'カミキリムシＧ' where byochu like '%カミキリ%' or byochu like '%マツクイムシ%';
update m_byochu set alias = 'ハモグリバエ・キモグリバエ・カラバエ・ミギワバエＧ' where byochu like '%カラバエ%' or byochu like '%キノコバエ%' or byochu like '%モグリバエ%' or byochu like '%ショウジョウバエ%';
update m_byochu set alias = 'キジラミＧ' where byochu like '%キジラミ%';
update m_byochu set alias = 'クスサンＧ' where byochu like '%クスサン%';
update m_byochu set alias = 'グンバイムシＧ' where byochu like '%グンバイ%';
update m_byochu set alias = 'コオロギ・カネタタキＧ' where byochu like '%コオロギ%' or byochu like '%カネタタキ%';
update m_byochu set alias = 'コナダニＧ' where byochu like '%コナダニ%';
update m_byochu set alias = 'コメツキムシＧ' where byochu like '%コメツキ%' or byochu like '%ハリガネムシ%';
update m_byochu set alias = 'サビダニＧ' where byochu like '%サビダニ%';
update m_byochu set alias = 'スカシバＧ' where byochu like '%スカシバ%';
update m_byochu set alias = 'タネバエ・ハナバエＧ' where byochu like '%タネバエ%' or byochu like 'ハナバエ';
update m_byochu set alias = 'ヤガＧ' where byochu like '%タバコガ%' or byochu like '%ネキリムシ%';
update m_byochu set alias = 'タマバエＧ' where byochu like '%タマバエ%';
update m_byochu set alias = 'タマバチＧ' where byochu like '%タマバチ%';
update m_byochu set alias = 'タマムシＧ' where byochu like '%タマムシ%';
update m_byochu set alias = 'トビムシＧ' where byochu like '%トビムシ%';
update m_byochu set alias = 'ナメクジＧ' where byochu like '%ナメクジ%';
update m_byochu set alias = 'ヨコバイＧ' where byochu like '%ヨコバイ%';
update m_byochu set alias = 'ハゴロモＧ' where byochu like '%ハゴロモ%';
update m_byochu set alias = 'カキヘタノムシガ・マイコガＧ' where byochu like '%マイコガ%' or byochu like '%カキヘタノムシガ%';
update m_byochu set alias = 'ムギダニＧ' where byochu like '%ムギダニ%';
update m_byochu set alias = 'ロウムシ・ロウカイガラムシＧ' where byochu like '%ロウムシ%' or '%ロウカイガラムシ%';
update m_byochu set alias = 'アザミウマＧ' where byochu like '%アザミウマ%';
update m_byochu set alias = 'カスミカメＧ' where byochu like '%カスミカメ%';
update m_byochu set alias = 'カブトムシＧ' where byochu like '%カブトムシ%';
update m_byochu set alias = 'キクイムシＧ' where byochu like '%キクイムシ%' or byochu like '%ヒメコシンクイ%';
update m_byochu set alias = 'ケシキスイＧ' where byochu like '%ケシキスイ%';
update m_byochu set alias = 'コウモリガＧ' where byochu like '%コウモリガ%';
update m_byochu set alias = 'コナジラミＧ' where byochu like '%コナジラミ%';
update m_byochu set alias = 'ホコリダニＧ' where byochu like '%ホコリダニ%';
update m_byochu set alias = 'シバンムシＧ' where byochu like '%シバンムシ%';
update m_byochu set alias = 'シャチホコガＧ' where byochu like '%シャチホコ%';
update m_byochu set alias = 'センチュウＧ' where byochu like '%センチュウ%';
update m_byochu set alias = 'タネコバチＧ' where byochu like '%タネコバチ%';
update m_byochu set alias = 'ダンゴムシＧ' where byochu like '%ダンゴムシ%';
update m_byochu set alias = 'チョッキリＧ' where byochu like '%チョッキリ%';
update m_byochu set alias = 'ハンミョウＧ' where byochu like '%ハンミョウ%';
update m_byochu set alias = 'ヒラタムシＧ' where byochu like '%ヒラタムシ%';
update m_byochu set alias = 'ヒロズコガＧ' where byochu like '%ヒロズコガ%';
update m_byochu set alias = 'リンゴガイＧ' where byochu like '%リンゴガイ%';
update m_byochu set alias = 'タマワタムシＧ' where byochu like '%タマワタムシ%';
update m_byochu set alias = 'ナガカメムシＧ' where byochu like '%ナガカメムシ%';
update m_byochu set alias = 'ハマキモドキＧ' where byochu like '%ハマキモドキ%';
update m_byochu set alias = 'ネアブラムシＧ' where byochu like '%ネアブラムシ%';
update m_byochu set alias = 'コナカイガラムシＧ' where byochu like '%コナカイガラムシ%';
update m_byochu set alias = 'クロバネキノコバエＧ' where byochu like '%クロバネキノコバエ%';
update m_byochu set alias = 'テントウムシダマシＧ' where byochu like '%テントウムシダマシ%' or byochu like '%ニジュウヤホシテントウ%';
--害虫Ｇ例外処理
update m_byochu set alias = 'アリＧ' where byochu = 'アイノキクイムシ';
update m_byochu set alias = 'カイガラムシＧ' where byochu = 'キュウコンコナカイガラムシ';
update m_byochu set alias = 'カスミカメＧ' where byochu like '%コバネナガガメムシ';
update m_byochu set alias = 'カメムシＧ' where byochu = 'ウスモンミドリカスミカメ';
update m_byochu set alias = 'タネバエ・ハナバエＧ' where byochu in ('ダイコンバエ', 'タマネギバエ');
update m_byochu set alias = 'タマワタムシＧ' where byochu = 'リンゴワタムシ';
update m_byochu set alias = 'トビムシＧ' where byochu = 'クワシロドクガ';
update m_byochu set alias = 'ネアブラムシＧ' where byochu = 'クリイガアブラムシ';
update m_byochu set alias = 'ハマキガＧ' where byochu in ('イグサシンムシガ', 'クリミガ');
update m_byochu set alias = 'ヒラタムシＧ' where byochu = 'カキミガ';
update m_byochu set alias = 'マイマイ・ドクガＧ' where byochu = 'ハラアカマイマイガ';
update m_byochu set alias = 'ヤガＧ' where byochu in ('タバコアオムシ', 'ナシケンモン', 'ベニモンアオリガ', 'ムラサキアシブト');
update m_byochu set alias = '' where cid = 3 and byochu like '%、%';
--害虫別名追加
update m_byochu set alias = concat(',', alias, 'イネアオムシ') where byochu = 'フタオビコヤガ';
update m_byochu set alias = concat(',', alias, 'ワタヘリクロノメイガ') where byochu = 'ウリノメイガ';
--病害別名追加
update m_byochu set alias = concat(',', alias, 'バンプビョウ') where byochu like '晩腐病%';
commit;
drop table if exists t_byochu;