MySQL/難解なSQL文〜whereにnot in、selectを使ってinsertする
こんな↓テーブル構成のDBがあるとします。
◎site_entry_categoriesテーブルのsite_entry_idにない値がsite_entriesテーブルのidに入っているデータのstatusを0にする。
(site_entry_idとidは同じデータが入る)
mysql> update site_entries set status = 0 where id not in ( select site_entry_id from site_entry_categories);
分解解説。
(1) update site_entries set status = 0 →site_entriesテーブルのstatusを0にする (2) where id not in ( select site_entry_id from site_entry_categories); →site_entry_categoriesテーブルのsite_entry_idの値と等しくない値がsite_entryiesテーブルのidに入っているsite_entryiesのデータ。
◎insert〜select構文(もう、呪文の様です/笑)
mysql> insert into site_entry_categories select v.id, (select id from site_categories where site_id = v.site_id and label = 'topic') as site_category_id, v.site_id, 0 as sort_order from ( select id,site_id,entry_id from site_entries where id not in ( select site_entry_id from site_entry_categories ) and entry_id = '10') v;
分解解説。
(1) insert into site_entry_categories →site_entry_categoriesにデータを入れたい (2) select →site_entry_categoriesテーブルの左側(縦にカラム名を並べた時は上から)のカラムから(3),(4),(5),(6)の順でデータを入れたい (3) v.id, →v.idを入れる((8)に当てはまるsite_entriesテーブルのデータのidを、site_entry_categoriesテーブルの一番左(一番上)のカラムのデータとして入れる) (4) (select id from site_categories where site_id = v.site_id and label = 'topic') as site_category_id, → site_categoriesテーブルのsite_idの値が(8)に当てはまるsite_entriesテーブルのデータのsite_idと等しい、かつ、site_categoriesテーブルのlabelにtopicと入っているsite_categoriesテーブルのidを、site_entry_categoriesテーブルのsite_category_id(site_entry_categoriesテーブルの左から(上から)2番目のカラム)に入れる (5) v.site_id, →v.site_idを入れる((8)に当てはまるsite_entriesテーブルのデータのsite_idを、site_entry_categoriesテーブルの左から(上から)3番目のカラムのデータとして入れる) (6) 0 as sort_order →site_entry_categoriesテーブルのsort_order(site_entry_categoriesテーブルの左から(上から)4番目のカラム)に0を入れる (7) from ( select id,site_id,entry_id from site_entries →(8)に当てはまるsite_entriesテーブルのデータのid, site_id, entry_idをv((9)より)とする (8) where id not in ( select site_entry_id from site_entry_categories) and entry_id = '10' →site_entry_categoriesテーブルのsite_entry_idの値と等しくない値がsite_entriesテーブルのidに入っているデータかつsite_entriesテーブルのentry_idが10であるデータ (9) ) v; →from以降のデータをvとする
◎ポイント
■where A not in (B); →Bと等しくないA 参考:where A in (B); Bと等しいA ■insert A select B; →テーブルAにBを左(上)から順番に入れていく(Bはコンマ区切りで並べたデータ) ■A as B →AをカラムBとする ■from (select A) B; →AをBとする。B.Aで括弧内(select A)部分のカラムデータが引き出せる。
これ、説明しにくいですね。。。分かりにくくてすみません。もっと分かりやすい書き方を思いついたときに書き直します。
こんなSQL文をほいほい書く先輩、恐ろしいと思った。。。