# create a table for collections if it doesn't exist
create table if not exists collection (
  collectionKey int auto_increment,
  resourceKey int not null,
  instCode varchar(255),
  collCode varchar(255),
  primary key (resourceKey, instCode, collCode),
  key(collectionKey)
);

# create all required collection entries that don't exist yet
insert ignore into collection (resourceKey, instCode, collCode)
select distinct bioRecordResourceKey, institution_code, collection_code
from bioRecord;

# then, enter the collection ID values into bioRecord
#update bioRecord
#straight_join collection on bioRecordResourceKey = resourceKey
#                        and institution_code     = instCode
#                        and collection_code      = collCode
#set bioRecordCollectionKey = collectionKey;
