USE current_bci;
INSERT INTO Species 
  (SpeciesID,CurrentTaxonFlag,ObsoleteTaxonFlag,GenusID,SpeciesName,Mnemonic,
  IDLevel,FieldFamily)
   VALUES 
  (1525,1,0,9999,'arecaceae-soberania','are1',
  'family','Arecaceae');
  

   
-- Change all xyl14 to xyl1pa -- 
-- First insert new species into Species table --
INSERT INTO Species 
  (SpeciesID,CurrentTaxonFlag,ObsoleteTaxonFlag,GenusID,SpeciesName,
   Mnemonic,IDLevel,FieldFamily,Authority)
   VALUES 
   (1517, 1, 0, 477, 'panamensis',
   'canaco','species','Annonaceae','G.E. Schatz'); 

-- Insert corresponding record in CurrentObsolete table --   
INSERT INTO CurrentObsolete 
   (SpeciesID,ObsoleteSpeciesID,ChangeDate,ChangeCodeID,ChangeNote) 
    VALUES 
   (1517, 1454, CURDATE(),'14', 'Species xyl14 has now been identified as xyl1pa');

-- Change CurrentTaxonFlag of old species to 0 to indicate it is now obsolete --  
UPDATE Species SET CurrentTaxonFlag=0, ObsoleteTaxonFlag=1 
     WHERE SpeciesID=1454 AND Mnemonic='xyl14';   

-- Change records in Tree table to correct species --
UPDATE Tree SET SpeciesID=1517 WHERE SpeciesID=1454;


-- Correct coordinates --
-- 132 recs --
INSERT INTO Log 
  (PersonnelID,ChangedTable,PrimaryKey,ChangedColumn,ChangeDate,ChangeTime,
   Description,Action,Old,New) 
   SELECT 78,'Stem', StemID,'QX',CURDATE(),NOW(),
  'Coordinates corrected','U',oldX, newX FROM correctLocations;    
INSERT INTO Log 
  (PersonnelID,ChangedTable,PrimaryKey,ChangedColumn,ChangeDate,ChangeTime,
   Description,Action,Old,New) 
   SELECT 'Stem',A.StemID,'QY',CURDATE(),NOW(),
   'Coordinates corrected','U',oldQY,newY FROM correctLocations;    
UPDATE Stem A, correctLocations B SET A.QX=B.X,A.QY=B.Y WHERE A.Treeid=B.TreeID;



-- Tag 520662 changed to 520661 which already exists --
INSERT INTO Log (PersonnelID,ChangedTable,PrimaryKey,ChangedColumn,ChangeDate,
   ChangeTime,Description,Action,Old,New) 
   SELECT 78,'DBH',DBHID,'StemID',CURDATE(),
   NOW(),'Tree is actually another tree that already exists','U',StemID,'418753' 
   FROM DBH WHERE StemID=1079742;   
UPDATE DBH SET StemID=418753 WHERE StemID=1079742; 
INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Stem','TreeID',StemID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',TreeID 
   FROM Stem WHERE StemID=1079742;      
INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Stem','QuadratID',StemID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',QuadratID 
   FROM Stem WHERE StemID=1079742;   
INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Stem','QX',StemID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',QX 
   FROM Stem WHERE StemID=1079742;      
INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Stem','QY',StemID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',QY 
   FROM Stem WHERE StemID=1079742;    
DELETE FROM Stem WHERE StemID=1079742;   

INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Tree','Tag',TreeID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',Tag 
   FROM Tree WHERE TreeID=527412;    
INSERT INTO Log (PersonnelID,ChangedTable,ChangedColumn,PrimaryKey,ChangeDate,
   ChangeTime,Description,Action,Old) 
   SELECT 78,'Tree','SpeciesID',TreeID,CURDATE(),
   NOW(),'Tree tag 520662=520661, which already exists','D',SpeciesID 
   FROM Tree WHERE TreeID=527412;       
DELETE FROM Tree WHERE TreeID=527412;   