text: use genesys_2018;
  
  
  create table CROP_INSTITUTION_GENUS_MLS_metric2 as 
  (select c.crop, g.institution_country, g.institution, 'N' as MLS_status, count(*) count
  FROM GMERGE_uniques g
  left join CIAT_crop_taxon c on (g.genus=c.taxon)
  where c.rank="genus"
  group by c.crop, g.institution_country, g.institution);
  
  -- Query OK, 17130 rows affected (6 min 24.18 sec)
  
  update CROP_INSTITUTION_GENUS_MLS_metric2 m
  set MLS_status ='Y'
  where m.crop in (select cl.Common_name_standard from CROP_MLS cl where cl.`MLS Annex 1`='Y' ) 
  and m.institution in (select inst.instcode from INSTITUTION_MLS inst  where inst.MLS=1 ) ;
  
  update CROP_INSTITUTION_GENUS_MLS_metric2 m
  set MLS_status ='Y'
  where m.institution_country in ("BEN089","CIV033","NER074","NER037","THA069","TWN001","TZA055","TWN012","TWN020","TWN016","UZB112",
  "PHL598" , "THA128","TWN024","VNM126","VNM133","BGD069","BTN047","CMR147","ETH096","BEL084","BEN084","CMR195","ITA406","KEN207","TGO168","NPL095",
  "TZA105","UGA332","GTM085","CRI001","CRI029","CRI026","CRI027","CRI085","CRI136","CRI134","CRI135","CRI142","CRI062","CRI033","HND020",
  "PAN132","SLV092","BOL061","BOL009","BOL228","COL003","ETH038","ETH015","HND119","MWI034","ARG1306","BOL230","CUB546","KEN179","LAO073",
  "PAN185","NIC036","PHL100","RWA011","THA126","TZA027","UGA034","PER334","TZA052","UGA139","ETH092","BFA035","BGD020","CRI063","ECU065",
  "GHA026","IND346","KAZ008","KEN030","MEX129","MEX052","MEX002","MEX064","AUS172","BEN080","DZA079","ECU070","ECU069","UGA466","NGA044",
  "NPL023","PHL094","PRY007","SYR035","THA065","TUR027","URY035","ZWE033","SLV062","SLV061","BOL038","MWI064","BDI007","BGD021","COL044",
  "CYP018","ECU057","IDN065","IDN165","IND232","KEN031","MWI067","ARG1305","PAN183","PER863","PER001","PER721","PHL097","RWA039","RWA012",
  "PER073","UGA252","VNM146","JOR090","EGY078","EGY217","LBN002","MEX126","AUS172","DZA052","OMN012","SYR035","SYR002","SYR043","UZB067",
  "MLI219","CMR081","CMR080","IDN172","KEN056","KEN023","KEN028","KEN207","SEN039","TZA078","PER486","IND922","ETH097","BFA001","IND132",
  "IND135","IND134","IND136","IND223","IND002","IND133","KEN032","MEX030","MLI072","MWI023","NER015","NER047","UGA480","NGA051","SDN019",
  "SEN108","SEN107","USA659","ZWE034","TGO092","USA646","CMR154","BEN027","BEN016","BFA034","BFA009","CIV065","CMR023","CMR057","COD014",
  "GBR055","GHA048","MWI065","TGO091","NGA042","NGA057","NGA039","NGA043","NGA081","RWA013","SLE008","UGA033","ZWE035","NGA119","ETH013",
  "KEN095","BEN055","ETH098","IND1308","UZB108","LKA189","BGD022","IDN066","IND296","KHM005","MDG012","MMR008","MMR122","MMR057","IDN153",
  "IND685","JPN089","LAO015","MYS167","NER092","UGA532","UGA173","NGA045","PHL001","PHL096","THA117","TZA028","VNM077");
  
  
  -- Query OK, 4999 rows affected (4.74 sec)
  
  
  create table CROP_INSTITUTION_SPECIES_MLS_metric2 as 
  (select c.crop, g.institution_country, g.institution, 'N' as MLS_status, count(*) count
  FROM GMERGE_uniques g
  left join CIAT_crop_taxon c on (g.species=c.taxon)
  where c.rank="species"
  group by c.crop, g.institution_country, g.institution);
  
  
  update CROP_INSTITUTION_SPECIES_MLS_metric2 m
  set MLS_status ='Y'
  where m.crop in (select cl.Common_name_standard from CROP_MLS cl where cl.`MLS Annex 1`='Y' ) 
  and m.institution in (select inst.instcode from INSTITUTION_MLS inst  where inst.MLS=1 ) ;
  
  update CROP_INSTITUTION_SPECIES_MLS_metric2 m
  set MLS_status ='Y'
  where m.institution_country in ("BEN089","CIV033","NER074","NER037","THA069","TWN001","TZA055","TWN012","TWN020","TWN016","UZB112",
  "PHL598" , "THA128","TWN024","VNM126","VNM133","BGD069","BTN047","CMR147","ETH096","BEL084","BEN084","CMR195","ITA406","KEN207","TGO168","NPL095",
  "TZA105","UGA332","GTM085","CRI001","CRI029","CRI026","CRI027","CRI085","CRI136","CRI134","CRI135","CRI142","CRI062","CRI033","HND020",
  "PAN132","SLV092","BOL061","BOL009","BOL228","COL003","ETH038","ETH015","HND119","MWI034","ARG1306","BOL230","CUB546","KEN179","LAO073",
  "PAN185","NIC036","PHL100","RWA011","THA126","TZA027","UGA034","PER334","TZA052","UGA139","ETH092","BFA035","BGD020","CRI063","ECU065",
  "GHA026","IND346","KAZ008","KEN030","MEX129","MEX052","MEX002","MEX064","AUS172","BEN080","DZA079","ECU070","ECU069","UGA466","NGA044",
  "NPL023","PHL094","PRY007","SYR035","THA065","TUR027","URY035","ZWE033","SLV062","SLV061","BOL038","MWI064","BDI007","BGD021","COL044",
  "CYP018","ECU057","IDN065","IDN165","IND232","KEN031","MWI067","ARG1305","PAN183","PER863","PER001","PER721","PHL097","RWA039","RWA012",
  "PER073","UGA252","VNM146","JOR090","EGY078","EGY217","LBN002","MEX126","AUS172","DZA052","OMN012","SYR035","SYR002","SYR043","UZB067",
  "MLI219","CMR081","CMR080","IDN172","KEN056","KEN023","KEN028","KEN207","SEN039","TZA078","PER486","IND922","ETH097","BFA001","IND132",
  "IND135","IND134","IND136","IND223","IND002","IND133","KEN032","MEX030","MLI072","MWI023","NER015","NER047","UGA480","NGA051","SDN019",
  "SEN108","SEN107","USA659","ZWE034","TGO092","USA646","CMR154","BEN027","BEN016","BFA034","BFA009","CIV065","CMR023","CMR057","COD014",
  "GBR055","GHA048","MWI065","TGO091","NGA042","NGA057","NGA039","NGA043","NGA081","RWA013","SLE008","UGA033","ZWE035","NGA119","ETH013",
  "KEN095","BEN055","ETH098","IND1308","UZB108","LKA189","BGD022","IDN066","IND296","KHM005","MDG012","MMR008","MMR122","MMR057","IDN153",
  "IND685","JPN089","LAO015","MYS167","NER092","UGA532","UGA173","NGA045","PHL001","PHL096","THA117","TZA028","VNM077");
  
  
  