## These are procedures to carry out specific corrections I see, now that panamaTree is the permanent database. I will no longer edit stagePanamaTree. At some point, I need to make the web app work to update the DB panamaTree, but at the moment (June 2019) it still edits only stagePanamaTree.
# SOURCE ~/data/treedatabase/TreeSpeciesPanama/PanTreeCorrection.sql


DELIMITER //

/*  This is a special case that causes problems when looking up Latin names. The one morphospecies Stemmadenia sp.1 was changed to Tabernaemontana sp.1 and given status syn. I change that to a new status, synmorph, to make sure it is not looked up as a real Latin name. */
DROP PROCEDURE IF EXISTS panamaTree.addSynMorph //
CREATE PROCEDURE panamaTree.addSynMorph(IN ModifyIt SMALLINT, IN AlterLatin CHAR(50))
BEGIN

    IF( ModifyIt = 1 )
     THEN  
       ALTER TABLE panamaTree.Species MODIFY Status ENUM('','good','question','syn','synadd','synrol','introducida','morphospecies','exclude','synmorph');
    END IF;

	SET @spIDqry=CONCAT("SET @spID=(SELECT DISTINCT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", AlterLatin, "')");
    PREPARE query FROM @spIDqry; EXECUTE query;
    UPDATE panamaTree.Species SET Status='synmorph' WHERE SpeciesID=@spID;

END //

/* Errors that somehow slipped through, where entire subspecies name is in species column. The case of S. simplex ochnacea is frustrating, since it is correct as a synonym. But I am not keeping subspecies that are synonyms in panamaTree. */
DROP PROCEDURE IF EXISTS panamaTree.fixGlaringError //
CREATE PROCEDURE panamaTree.fixGlaringError(IN ChooseLatin VARCHAR(32), IN newstatus CHAR(50))
BEGIN

    SET @badDio=(SELECT SpeciesID FROM panamaTree.Species WHERE SpeciesName="juruensis subsp. hartmanniana" AND GenusID=154);
    SET @badTetra=(SELECT SpeciesID FROM panamaTree.Species WHERE SpeciesName="gorgonae subsp. robledoanum" AND GenusID=442);
    SET @badSwartz=(SELECT SpeciesID FROM panamaTree.Species WHERE SpeciesName="simplex var. ochnacea" AND GenusID=422);
    DELETE FROM panamaTree.Species WHERE SpeciesID IN (@badDio,@badTetra,@badSwartz);
    
    SET @fixDio=(SELECT SpeciesID FROM panamaTree.Species WHERE SpeciesName="juruensis subsp. panamensis" AND GenusID=154);
    UPDATE panamaTree.Species SET SpeciesName='juruensis' WHERE SpeciesID=@fixDio;

END //


/*  A query allowing any Latin name to be assigned a new status. */
DROP PROCEDURE IF EXISTS panamaTree.editStatus //
CREATE PROCEDURE panamaTree.editStatus(IN ChooseLatin VARCHAR(32), IN newstatus CHAR(50))
BEGIN

	SET @spIDqry=CONCAT("SET @spID=(SELECT DISTINCT SpeciesID FROM panamaTree.ViewLatin WHERE Latin='", ChooseLatin, "'");
    # PREPARE query FROM @spIDqry; EXECUTE query;
    SET @updateQry=CONCAT("UPDATE panamaTree.Species SET Status='", newstatus, "' WHERE SpeciesID=@spID");
    # PREPARE query FROM @updateQry; EXECUTE query;
    SELECT @spIDqry, @updateQry;

END //


/*  Errors in Synonym. These showed up when extracting Tropicos coordinates and consulting synonomy to match names. I executed on 9 June 2019.
Rheedia edulis is unfortunate, but because Garcinia intermedia was split into 2, it is no longer possible to assign Rheedia edulis to either one.
In both Borojoa and Ossaea, I failed to correct old synonyms after recent updates. Worse, though, is that I slipped up and left Clidemia quinquenervia as status=good even though I added the new Miconia. 
Tovomitopsis is messed up because it was used incorrectly on BCI. It should be one of the BCI exceptions excluded from Synonym table. 
Geonoma oxycarpa was one of many errors in Geonoma that Rolando had because Henderson reversed many in 2011, but I missed this one.
Cephaelis tomentosa is my own bone-headed typo. It should be P. tomentosa, not P. tonduzii.
Nectandra purpurea with D. umbrosa is another fuck-up from 50-ha plot changes. 
And Guarea glabra ternifoliola is just stupid. 
Now I see that there are quite a few synonyms Rolando noted, but that I ignored because they weren't in BIEN, and are now relevant because Tropicos has records:
Ouratea pyramidalis -> Ouratea insulae
Stemmadenia macrophylla -> Stemmadenia littoralis -> Tabernaemontana littoralis
Tovomitopsis croatii -> Tovomita croatii
*/
DROP PROCEDURE IF EXISTS panamaTree.fixSyns //
CREATE PROCEDURE panamaTree.fixSyns()
BEGIN

   SET @badOld=NULL; SET @goodVal=NULL; SET @badVal=NULL;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Rheedia edulis');
   DELETE FROM Synonym WHERE SpeciesID=@badOld;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Guarea ternifoliola');
   DELETE FROM Synonym WHERE SpeciesID=@badOld OR ValidSpeciesID=@badOld;
   
   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Borojoa panamensis');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Alibertia dwyeri');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Cephaelis tomentosa');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Palicourea tonduzii');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Nectandra purpurea');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Damburneya umbrosa');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Geonoma oxycarpa');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Geonoma interrupta');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;

   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Tovomitopsis nicaraguensis');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Chrysochlamys eclipes');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;
     
   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Ossaea quinquenervia');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Clidemia quinquenervia');
   SET @goodVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Miconia quinquenervia');
   DELETE FROM Synonym WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;
   INSERT INTO Synonym (SpeciesID,ValidSpeciesID) VALUES (@badVal,@goodVal);
   UPDATE Species SET Status='syn' WHERE SpeciesID=@badVal;
   
   SET @badOld=(SELECT SpeciesID FROM viewLatin WHERE Latin='Clusia oblanceolata');
   SET @badVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Tovomita weddelliana');
   SET @goodVal=(SELECT SpeciesID FROM viewLatin WHERE Latin='Arawakia weddelliana');
   UPDATE Synonym SET ValidSpeciesID=@goodVal WHERE ValidSpeciesID=@badVal AND SpeciesID=@badOld;
   
END //

/*
This is bad. I need to update the web app to edit panamaTree. This is ridiculous how long a query like this takes...
DROP PROCEDURE IF EXISTS panamaTree.addSynName //
CREATE PROCEDURE panamaTree.addSynName(IN NewGenus VARCHAR(32), IN NewSpecies VARCHAR(32), In NewAuth VARCHAR(100), IN ValidLatin CHAR(50))
BEGIN

    SET @valIDqry=CONCAT("SET @goodVal=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", ValidLatin, "'");
    PREPARE query FROM @valIDqry; EXECUTE query;
    SET @valGenqry=CONCAT("SET @genID=(SELECT GenusID FROM panamaTree.Genus WHERE Genus='", NewGenus, "'");
    PREPARE query FROM @valGenqry; EXECUTE query;

    SET @addSpqry=CONCAT("INSERT INTO panamaTree.Species (GenusID,SpeciesName,Authority,Status,Year_added) VALUES (@genID,'", 
                         NewSpecies, "', '", NewAuth, "', 'syn', '2019')");
    # PREPARE query FROM @addSpqry; EXECUTE query;
    SELECT @valIDqry, @valGenqry, @addSpqry;
                        
    SET @synIDqry=CONCAT("SET @synVal=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", CONCAT(NewGenus,' ',NewSpecies), "'");
    # INSERT INTO panamaTree.Synonym (SpeciesID,ValidSpeciesID,Country) VALUES (@synVal,@goodVal,''); 

END //
*/

/* Spelling errors I let get through */
DROP PROCEDURE IF EXISTS panamaTree.fixLycIneq //
CREATE PROCEDURE panamaTree.fixLycIneq()
BEGIN

   SET @badOld=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Lycianthes inequilatera');
   UPDATE Species SET SpeciesName='inaequilatera' WHERE SpeciesID=@badOld;

END //

/* Obvious error -- I put wrong valid name -- that I never caught before Aug 16, 2019. */
DROP PROCEDURE IF EXISTS panamaTree.fixCryptocarya //
CREATE PROCEDURE panamaTree.fixCryptocarya()
BEGIN

   SET @Cpan=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Cryptocarya panamensis');
   SET @Cash=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Cryptocarya aschersoniana');
   UPDATE Synonym SET Country='Panama' WHERE SpeciesID=@Cash AND ValidSpeciesID=@Cpan;
   DELETE FROM Synonym WHERE SpeciesID=@Cash AND ValidSpeciesID=@Cash;

END //

/* spelling error: Lonchocarpus unifoliatus. And Rolando has it correct! */
DROP PROCEDURE IF EXISTS panamaTree.fixLonchUni //
CREATE PROCEDURE panamaTree.fixLonchUni()
BEGIN

   SET @badOld=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Lonchocarpus unifoliatus');
   SET @good=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Lonchocarpus unifoliolatus');
   UPDATE Species SET Status='good' WHERE SpeciesID=@good;
   UPDATE Species SET Status='syn' WHERE SpeciesID=@badOld;
   UPDATE Synonym SET ValidSpeciesID=@good WHERE ValidSpeciesID=@badOld;
   INSERT INTO Synonym (SpeciesID,ValidSpeciesID,country) VALUES(@badOld,@good,'');

END //

/* Somehow I find Aulomyrcia zetekiana appearing as synonym for both M. tomentosa and M. zetekiana. It is correct in the stagePanamaTree table. 
   E. zetekiana also incorrectly linked to M. tomentosa. There must have been a time when M. zetekiana was synonym of M. tomentosa?
   Executed this 29 Aug 2019.
   30 Apr 2020: I re-detected the same errors. Evidently I did not execute this?
   I executed and checked that the synonyms are corrected on 30 Apr 2020. */
DROP PROCEDURE IF EXISTS panamaTree.fixAulomyrcia //
CREATE PROCEDURE panamaTree.fixAulomyrcia()
BEGIN

   SET @badSyn=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Myrcia tomentosa');
   SET @mainAulo=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Aulomyrcia zetekiana');
   SET @goodSyn=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Myrcia zetekiana');
   SET @mainEuge=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Eugenia zetekiana');
   DELETE FROM Synonym WHERE SpeciesID=@mainAulo AND ValidSpeciesID=@badSyn;
   UPDATE Synonym SET ValidSpeciesID=@goodSyn WHERE SpeciesID=@mainEuge;

END //

/* This was just stupidity. I decided to add updates on 30 Aug 2019, including mostly Myrcia and Conostegia. It turns out to be so messy. In this case, I forgot to check that one of the Conostegia was already in the database as a synrol, so I mistakenly duplicated it. */
DROP PROCEDURE IF EXISTS panamaTree.fixConostegia //
CREATE PROCEDURE panamaTree.fixConostegia()
BEGIN

   SET @badSp=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Conostegia fragrantissima' AND SpeciesID<8000);
   SET @goodSp=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Conostegia fragrantissima' AND SpeciesID>=8000);
   SET @badSyn=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Conostegia montana');
   DELETE FROM Synonym WHERE SpeciesID=@badSp AND ValidSpeciesID=@badSyn;
   DELETE FROM Species WHERE SpeciesID=@badSp;
   # SELECT * FROM Species WHERE SpeciesID=@goodSp;

END //


/* Aug 2019. Odd spellings I had missed due to disagreement on which is valid. 
This was executed early Aug 2019.*/
DROP PROCEDURE IF EXISTS panamaTree.fixMoreSyns //
CREATE PROCEDURE panamaTree.fixMoreSyns()
BEGIN

   SET @badOld=NULL; SET @goodVal=NULL; SET @badVal=NULL;

   SET @newSyn="'Ocotea valerioana','Miconia valerioana','Cleyera theoides','Aegiphila valerioi','Clusia valerioi','Hoffmannia valerioi'";
   SET @newVal="'Ocotea valeriana','Miconia valeriana','Cleyera theaeoides','Aegiphila valerii','Clusia valerii','Hoffmannia valerii'";

    SET @x=1;
    theloop: WHILE 1 DO
    
        SET @getSyn=CONCAT('SET @syn=ELT(', @x, ', ', @newSyn, ')');
        SET @getVal=CONCAT('SET @val=ELT(', @x, ', ', @newVal, ')');
        PREPARE query FROM @getSyn; EXECUTE query; 
        PREPARE query FROM @getVal; EXECUTE query; 

        IF @syn IS NULL THEN
          LEAVE theloop; 
        ELSE SET @x=@x+1;
        END IF;
        
        SET @getSynID=CONCAT("SET @synID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='",@syn,"')");
        SET @getValID=CONCAT("SET @validID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='",@val,"')");
        PREPARE query FROM @getSynID; EXECUTE query; 
        PREPARE query FROM @getValID; EXECUTE query; 
        # SELECT @syn, @synID, @val, @validID, @x;

        UPDATE Species SET Status='syn' WHERE SpeciesID=@synID;
        UPDATE Synonym SET ValidSpeciesID=@validID WHERE ValidSpeciesID=@synID;
        INSERT INTO Synonym (SpeciesID,ValidSpeciesID) VALUES (@synID,@validID);

    END WHILE;  

    SET @synID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Cyathea delgadoi');
    SET @validID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Cyathea delgadii');
    INSERT INTO Synonym (SpeciesID,ValidSpeciesID) VALUES (@synID,@validID);
    UPDATE Synonym SET country='' WHERE country IS NULL;
    
END //

/* Aug 2019. Now convert this to a general way to redo synonymy. 
CALL fixSynsGen("'Cosmibuena valerioi','Hillia valerii'","'Cosmibuena valerii','Cosmibuena valerii'")
This was finished 11 Aug 2019.
Used again Sept 2020 for Cestrum I missed, and it worked perfectly.
CALL fixSynsGen("'Cestrum haberi'","'Cestrum rugulosum'")
Here I had to add the valid name first, with addGenSp (which I had to create).
CALL fixSynsGen("'Adelobotrys panamensis'","'Meriania compressicaulis'")
*/
DROP PROCEDURE IF EXISTS panamaTree.fixSynsGen //
CREATE PROCEDURE panamaTree.fixSynsGen(IN newSyn VARCHAR(500), IN newVal VARCHAR(500))

BEGIN

    SET @x=1;
    theloop: WHILE 1 DO
    
        SET @getSyn=CONCAT('SET @syn=ELT(', @x, ', ', newSyn, ')');
        SET @getVal=CONCAT('SET @val=ELT(', @x, ', ', newVal, ')');
        PREPARE query FROM @getSyn; EXECUTE query; 
        PREPARE query FROM @getVal; EXECUTE query; 
        
        IF @syn IS NULL THEN
          LEAVE theloop; 
        ELSE SET @x=@x+1;
        END IF;
        
        SET @getSynID=CONCAT("SET @synID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='",@syn,"')");
        SET @getValID=CONCAT("SET @validID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='",@val,"')");
        PREPARE query FROM @getSynID; EXECUTE query; 
        PREPARE query FROM @getValID; EXECUTE query; 
        SELECT @syn, @synID, @val, @validID, @x;

        UPDATE Species SET Status='syn' WHERE SpeciesID=@synID;
        UPDATE Synonym SET ValidSpeciesID=@validID WHERE ValidSpeciesID=@synID;
        INSERT INTO Synonym (SpeciesID,ValidSpeciesID) VALUES (@synID,@validID);

    END WHILE;  

    UPDATE Synonym SET country='' WHERE country IS NULL;
    
END //

/* 
I haven't yet made an option to simply add a new taxon. Status is automatically good.
CALL addGenSp("'Meriania'","'compressicaulis'","'Almeda & Penneys'","'3.5 m. Molecular revision (Almeda & Penneys 2014) moves two Adelobotrys to Meriania.'","'Almeda2014'")
CALL addGenSp("'Triumfetta'","'calderonii'","'Stand'","'Added as synonym of T. arborescens'","'TiliaceaeManCR'")
*/

DROP PROCEDURE IF EXISTS panamaTree.addGenSp //
CREATE PROCEDURE panamaTree.addGenSp(IN newGen VARCHAR(500), IN newSp VARCHAR(500), IN auth VARCHAR(500), IN note VARCHAR(500), IN cite VARCHAR(500))

BEGIN

    SET @x=1;
    theloop: WHILE 1 DO
    
        SET @getGenus=CONCAT('SET @genus=ELT(', @x, ', ', newGen, ')');
        SET @getSpecies=CONCAT('SET @species=ELT(', @x, ', ', newSp, ')');
        SET @getAuth=CONCAT('SET @authority=ELT(', @x, ', ', auth, ')');
        SET @getNote=CONCAT('SET @notes=ELT(', @x, ', ', note, ')');
        SET @getRef=CONCAT('SET @citation=ELT(', @x, ', ', cite, ')');
        PREPARE query FROM @getGenus; EXECUTE query; 
        PREPARE query FROM @getSpecies; EXECUTE query; 
        PREPARE query FROM @getAuth; EXECUTE query; 
        PREPARE query FROM @getNote; EXECUTE query; 
        PREPARE query FROM @getRef; EXECUTE query; 
        
        IF @genus IS NULL THEN
          LEAVE theloop; 
        ELSE SET @x=@x+1;
        END IF;
        
        SET @getGenusID=CONCAT("SET @genID=(SELECT GenusID FROM panamaTree.Genus WHERE Genus='",@genus,"')");
        SET @doInsert=CONCAT("INSERT INTO panamaTree.Species (GenusID,SpeciesName,Authority,Status,Notes,Reference) ",
                           "VALUES (", @genID, ",'", @species, "','", @authority, "','good','", @notes, "','", @citation, "')");
        PREPARE query FROM @getGenusID; EXECUTE query; 
        PREPARE query FROM @doInsert; EXECUTE query; 
        SELECT @genus, @species, @genID, @doInsert, @x;

    END WHILE;  
    
END //


/*
CALL editNotes('Aphelandra merelloae','1.9 m. Described 2014.','Notes',1);
CALL editNotes('Aphelandra merelloae','Daniel2014','Reference',1);
CALL editNotes('Adelobotrys panamensis','Moved to Meriania','Notes',1);
*/
DROP PROCEDURE IF EXISTS panamaTree.editNotes //
CREATE PROCEDURE panamaTree.editNotes(IN latin VARCHAR(100), IN newvalue text, IN whichcol VARCHAR(12), IN replaceit INT)
BEGIN

    SET @spIDqry=CONCAT("SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", latin, "')");
    PREPARE query FROM @spIDqry; EXECUTE query; 
    SET @existingQry=CONCAT("SET @existing=(SELECT ", whichcol, " FROM panamaTree.Species WHERE SpeciesID=@spID)");
    PREPARE query FROM @existingQry; EXECUTE query; 

    IF replaceit = 1 THEN
      SET @updateQry=CONCAT("UPDATE panamaTree.Species SET ", whichcol, "='", newvalue, "' WHERE SpeciesID=@spID");
    ELSE SET @updateQry=CONCAT("UPDATE panamaTree.Species SET ", whichcol, "=CONCAT(@existing,'", newvalue, "') WHERE SpeciesID=@spID");
    END IF;

    PREPARE query FROM @updateQry; EXECUTE query; 
    SELECT @updateQry;
       
END //


/*  This correction was exceedingly tedious, brought about by further stupidity.  See checkRolandoSyn in PanamaSynonomy.r.
This creates a table. Unfortunately, LOAD DATA is not allowed in procedures.
*/

DROP PROCEDURE IF EXISTS panamaTree.fixRolandoSyn //
CREATE PROCEDURE panamaTree.fixRolandoSyn(IN finish INT)
BEGIN

  DROP TABLE IF EXISTS tempRolSyn;
  SET @loadqry="LOAD DATA LOCAL INFILE '~/data/treedatabase/TreeSpeciesPanama/MissingSynRol.csv' INTO TABLE tempRolSyn CHARACTER SET UTF8 IGNORE 1 LINES"; 

  IF finish = 0 THEN
      CREATE TABLE tempRolSyn
       (
         synrol VARCHAR(200),
         Family VARCHAR(200),
         NewStatus VARCHAR(200),
         Valid VARCHAR(200),
         Notes VARCHAR(200),
         country VARCHAR(200) DEFAULT '',
         SpeciesID VARCHAR(200),
         Status VARCHAR(200),
         ValidSpeciesID VARCHAR(200),
         ValidStatus VARCHAR(200),
         INDEX(synrol),
         INDEX(SpeciesID),
         INDEX(Valid),
         INDEX(ValidSpeciesID)
       ) ENGINE=MyISAM;
      SELECT @qry;
  END IF;

END //


/* Psychotria mima and Vismia guianensis got into synonym table, but both names cannot be matched to valid names in Panama. They have status=exclude, but should not be assigned valid names. T. sananho was used in our plots, but that was corrected and it can't be listed as syn. E. uniflora is not native to Panama, so I should not have it listed as non-native in the US. I excluded it because it is not in Fl Meso. This deletes from Synonym table. */
DROP PROCEDURE IF EXISTS panamaTree.fix3MoreSyns //
CREATE PROCEDURE panamaTree.fix3MoreSyns()
BEGIN
 
    SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin = 'Psychotria mima');
    DELETE FROM Synonym WHERE SpeciesID=@spID;
    SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin = 'Vismia guianensis');
    DELETE FROM Synonym WHERE SpeciesID=@spID;
    SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin = 'Tabernaemontana sananho');
    DELETE FROM Synonym WHERE SpeciesID=@spID;
    SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin = 'Eugenia uniflora');
    DELETE FROM Synonym WHERE SpeciesID=@spID;

END //

/* Sept 2025. Working on tree guide again I found I failed to move mnemonic from Guatteria recurvisepala to the valid G. ucayalina. I have no record of adding the latter, so I can't tell how this happened.*/
DROP PROCEDURE IF EXISTS panamaTree.fixSynCode //
CREATE PROCEDURE panamaTree.fixSynCode()
BEGIN
 
    SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin = 'Guatteria ucayalina');
    SET @correct=(SELECT Mnem FROM panamaTree.viewLatin WHERE Latin = 'Guatteria recurvisepala');
    UPDATE panamaTree.Species SET Mnemonic=@correct WHERE SpeciesID=@spID;

END //


DELIMITER ;



