害虫グループ付きの m_byouchu です。グループ分けがちゃんとできているかどうかは、例によって検証していません。１か所、'%ハナバエ%' とすべきところが 'ハナバエ' になっていたのは修正しました。
また、ふりがな機能が未実装なので、今回は別名登録はしていません。

2010.11.4 9:30
「グループ無し」の処理を追加、複数害虫の処理変更

--/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, gid 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 ('イノシシ', 'カモシカ', 'ニホンジカ', 'モグラ', '野ウサギ', '野ソ', '害獣');
--害虫Ｇ初期化
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'グループ無し') where cid = (select cid from spec.cbyochu where cname = '害虫');
--害虫Ｇ優先処理1
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アリＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%アリ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ケラＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%ケラ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コガＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%コガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'セミＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%ゼミ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ダニＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%ダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヤガＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%ヤガ%';
--害虫Ｇ優先処理2
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'スズメガＧ') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%スズメ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アゲハＧ') where byochu like '%アゲハ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ケムシＧ') where byochu like '%ケムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'イラガＧ') where byochu like '%イラガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ウスバＧ') where byochu like '%ウスバ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ウンカＧ') where byochu like '%ウンカ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カレハガＧ') where byochu like '%カレハ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'キバチＧ') where byochu like '%キバチ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'シジミチョウＧ') where byochu like '%シジミ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'シャクガＧ') where byochu like '%シャク%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'チビガＧ') where byochu like '%チビガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'トラガＧ') where byochu like '%トラガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'トリバガＧ') where byochu like '%トリバ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ネダニＧ') where byochu like '%ネダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハダニＧ') where byochu like '%ハダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハバチＧ') where byochu like '%ハバチ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハムシＧ') where byochu like '%ハムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヒトリガＧ') where byochu like '%ヒトリ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ミバエＧ') where byochu like '%ミバエ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヨトウＧ') where byochu like '%ヨトウ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'イナゴ・バッタＧ') where byochu like '%イナゴ%' or byochu like '%バッタ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ウワバＧ') where byochu like '%ウワバ%' or byochu like '%シタバ%' or byochu like '%キノカワガ' or byochu like '%ツメクサガ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コガネムシ・ブイブイＧ') where byochu like '%コガネ%' or byochu like '%ドウガネ%' or byochu like 'ハナムグリ' or byochu like '%ブイブイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コナガ・スガＧ') where byochu like '%コナガ%' or byochu like '%スムシ%' ;
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ミノガ・ミノムシＧ') where byochu like '%ミノガ%' or byochu like '%ミノムシ%' ;
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ツトガＧ') where byochu like '%ツトガ%' or byochu like '%ツトムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'メイガＧ') where byochu like '%メイガ%' or byochu like '%メイチュウ%' or byochu like '%ツヅリガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ホソガ・カワモグリＧ') where byochu like '%ホソガ%' or byochu like '%カワモグリ%'; -- ハマキガより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'シンクイガ・シンクイムシＧ') where byochu like '%シンクイ%'; -- キクイムシより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハマキガＧ') where byochu like '%ハマキ%' or byochu like '%サヤムシ%' or byochu like '%カワモグリガ%'; -- ハマキモドキより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'マイマイ・カタツムリＧ') where byochu like '%マイマイ%' or byochu like '%カタツムリ%'; -- マイマイガより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'マイマイ・ドクガＧ') where byochu like '%ドクガ%' or byochu like '%マイマイガ%';
--別名優先処理3
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カメムシＧ') where byochu like '%カメムシ%'; -- ナガカメムシより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ゾウムシＧ') where byochu like '%ゾウムシ%'; -- チョッキリより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'テントウムシＧ') where byochu like '%テントウ%'; -- テントウムシダマシより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハモグリガＧ') where byochu like '%ハモグリ%'; -- ハモグリバエより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アブラムシＧ') where byochu like '%アブラムシ%' or byochu like '%ワタムシ%'; -- ネアブラムシ、タマワタムシより前に実行
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カイガラムシＧ') where byochu like '%カイガラムシ%'; -- コナカイガラムシ、ロウカイガラムシより前に実行
--害虫Ｇ通常処理
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アオムシＧ') where byochu like '%アオムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ジャガイモガ・イモコガＧ') where byochu like '%イモコガ%' or byochu like '%ジャガイモガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ガガンボＧ') where byochu like '%ガガンボ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カミキリムシＧ') where byochu like '%カミキリ%' or byochu like '%マツクイムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハモグリバエ・キモグリバエ・カラバエ・ミギワバエＧ') where byochu like '%カラバエ%' or byochu like '%キノコバエ%' or byochu like '%モグリバエ%' or byochu like '%ショウジョウバエ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'キジラミＧ') where byochu like '%キジラミ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'クスサンＧ') where byochu like '%クスサン%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'グンバイムシＧ') where byochu like '%グンバイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コオロギ・カネタタキＧ') where byochu like '%コオロギ%' or byochu like '%カネタタキ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コナダニＧ') where byochu like '%コナダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コメツキムシＧ') where byochu like '%コメツキ%' or byochu like '%ハリガネムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'サビダニＧ') where byochu like '%サビダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'スカシバＧ') where byochu like '%スカシバ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タネバエ・ハナバエＧ') where byochu like '%タネバエ%' or byochu like '%ハナバエ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヤガＧ') where byochu like '%タバコガ%' or byochu like '%ネキリムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タマバエＧ') where byochu like '%タマバエ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タマバチＧ') where byochu like '%タマバチ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タマムシＧ') where byochu like '%タマムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'トビムシＧ') where byochu like '%トビムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ナメクジＧ') where byochu like '%ナメクジ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヨコバイＧ') where byochu like '%ヨコバイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハゴロモＧ') where byochu like '%ハゴロモ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カキヘタノムシガ・マイコガＧ') where byochu like '%マイコガ%' or byochu like '%カキヘタノムシガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ムギダニＧ') where byochu like '%ムギダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ロウムシ・ロウカイガラムシＧ') where byochu like '%ロウムシ%' or '%ロウカイガラムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アザミウマＧ') where byochu like '%アザミウマ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カスミカメＧ') where byochu like '%カスミカメ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カブトムシＧ') where byochu like '%カブトムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'キクイムシＧ') where byochu like '%キクイムシ%' or byochu like '%ヒメコシンクイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ケシキスイＧ') where byochu like '%ケシキスイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コウモリガＧ') where byochu like '%コウモリガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コナジラミＧ') where byochu like '%コナジラミ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ホコリダニＧ') where byochu like '%ホコリダニ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'シバンムシＧ') where byochu like '%シバンムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'シャチホコガＧ') where byochu like '%シャチホコ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'センチュウＧ') where byochu like '%センチュウ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タネコバチＧ') where byochu like '%タネコバチ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ダンゴムシＧ') where byochu like '%ダンゴムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'チョッキリＧ') where byochu like '%チョッキリ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハンミョウＧ') where byochu like '%ハンミョウ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヒラタムシＧ') where byochu like '%ヒラタムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヒロズコガＧ') where byochu like '%ヒロズコガ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'リンゴガイＧ') where byochu like '%リンゴガイ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タマワタムシＧ') where byochu like '%タマワタムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ナガカメムシＧ') where byochu like '%ナガカメムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハマキモドキＧ') where byochu like '%ハマキモドキ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ネアブラムシＧ') where byochu like '%ネアブラムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'コナカイガラムシＧ') where byochu like '%コナカイガラムシ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'クロバネキノコバエＧ') where byochu like '%クロバネキノコバエ%';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'テントウムシダマシＧ') where byochu like '%テントウムシダマシ%' or byochu like '%ニジュウヤホシテントウ%';
--害虫Ｇ例外処理
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'アリＧ') where byochu = 'アイノキクイムシ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カイガラムシＧ') where byochu = 'キュウコンコナカイガラムシ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カスミカメＧ') where byochu like '%コバネナガガメムシ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'カメムシＧ') where byochu = 'ウスモンミドリカスミカメ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タネバエ・ハナバエＧ') where byochu in ('ダイコンバエ', 'タマネギバエ');
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'タマワタムシＧ') where byochu = 'リンゴワタムシ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'トビムシＧ') where byochu = 'クワシロドクガ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ネアブラムシＧ') where byochu = 'クリイガアブラムシ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ハマキガＧ') where byochu in ('イグサシンムシガ', 'クリミガ');
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヒラタムシＧ') where byochu = 'カキミガ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'マイマイ・ドクガＧ') where byochu = 'ハラアカマイマイガ';
update m_byochu set gid = (select gid from spec.ggaichu where gname = 'ヤガＧ') where byochu in ('タバコアオムシ', 'ナシケンモン', 'ベニモンアオリガ', 'ムラサキアシブト');
update m_byochu set gid = (select gid from spec.ggaichu where gname = '複数害虫') where cid = (select cid from spec.cbyochu where cname = '害虫') and byochu like '%、%';
commit;
drop table if exists t_byochu;