# create a table with a full outer join list
create temporary table tmp

select corm_datasource.serviceKey, corm_datasource.european_data_only as flag_new, cormList.european_data_only as flag_old
from corm_datasource left join  cormList using (serviceKey)

  union

select cormList.serviceKey, corm_datasource.european_data_only as flag_new, cormList.european_data_only as flag_old
from corm_datasource right join cormList using (serviceKey);

# now select all services whose flag has changed and key the respective bioRecordKeys
create temporary table tmp2
select straight_join bioRecordKey, bioRecordModifiedDate
from tmp
join GBIF.bioRecord  on bioRecordServiceKey = serviceKey
where (flag_new <> flag_old)
   or (flag_new is null and flag_old is not null)
   or (flag_old is null and flag_new is not null);

# Now, insert these records into the to-do-lists
insert ignore into removedRecords
select bioRecordKey from tmp2
join bioRecordList on brKey = bioRecordKey;

replace into updatedRecords
select bioRecordKey, bioRecordModifiedDate from tmp2;