# First, count the taxa and update the counts in table "taxon"
create temporary table tmpTaxa
select taxonKey as tKey, count(*) as uCount
from taxon
join bioRecord on bioRecordTaxonKey = taxonKey
group by taxonKey;

alter table tmpTaxa	modify uCount int,	add key (tKey);

update low_priority taxon
join tmpTaxa on tKey = taxonKey
set unitCount = uCount;

# then, create all the suggest tables with prefix "tmp"
create table tmpTaxon
select taxonName as Name, sum(unitCount) as unitCount
from taxon
group by taxonName;

create table tmpGenus
select genus as Name, sum(unitCount) as unitCount
from taxon
group by genus;

create table tmpFamily
select family as Name, sum(unitCount) as unitCount
from taxon
group by family;

create table tmpHigherTaxon
select higherTaxonName as Name, sum(unitCount) as unitCount
from higherTaxon
join taxon using (taxonKey)
group by higherTaxonName;

create table tmpVernacular
select vernacularName as Name, sum(unitCount) as unitCount
from vernacularName
join taxon on vernacularTaxonKey = taxonKey
group by vernacularName;

create table tmpCountry
select countryName as Name, count(*) as unitCount
from isoCountry
join bioRecord on bioRecordCountryCode = isoCountryCode
group by countryName;

# remove invalid entries
delete from tmpFamily 			where Name is null or Name = '';
delete from tmpGenus 				where Name is null or Name = '';
delete from tmpHigherTaxon 	where Name is null or Name = '';

# last, drop old tables and rename tmp tables, creating index

alter table tmpTaxon       rename to suggestTaxon,       modify unitCount int, add index (Name);
alter table tmpGenus       rename to suggestGenus,       modify unitCount int, add index (Name);
alter table tmpFamily      rename to suggestFamily,      modify unitCount int, add index (Name);
alter table tmpHigherTaxon rename to suggestHigherTaxon, modify unitCount int, add index (Name);
alter table tmpVernacular  rename to suggestVernacular,  modify unitCount int, add index (Name);
alter table tmpCountry     rename to suggestCountry,     modify unitCount int, add index (Name);