bug #6938
Updated by Andreas Müller almost 6 years ago
Not yet complete
* run [s_em_MoveCommonNames] to move common names to structured common names
===
remove duplicate parent relationships (see also http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication)
~~~ sql
SELECT *
FROM (
SELECT COUNT(pt2.RIdentifier) AS HowMany, pt.PTNameFk, pt.PTRefFk , n.FullNameCache
FROM PTaxon
INNER JOIN RelPTaxon rel ON pt.PTNameFk = rel.PTNameFk1 AND pt.PTRefFk = rel.PTRefFk1
INNER JOIN PTaxon pt2 ON rel.PTNameFk2 = pt2.PTNameFk AND rel.PTRefFk2 = pt2.PTRefFk
INNER JOIN Name n ON pt.PTNameFk = n.NameID
WHERE (rel.RelQualifierFk = 1) AND (pt.PTRefFk % 100000 = 0) AND (pt.PTRefFk >= 7000000)
GROUP BY pt.RIdentifier, pt.PTNameFk, pt.PTRefFk, n.FullNameCache) AS DuplicateParentRelation
WHERE HowMany > 1
ORDER BY HowMany DESC;
~~~
~~~ sql
SELECT n2.fullNameCache, rel.*
FROM PTaxon pt
INNER JOIN RelPTaxon rel ON pt.PTNameFk = rel.PTNameFk1 AND pt.PTRefFk = rel.PTRefFk1
INNER JOIN Name n2 ON rel.PTNameFk2 = n2.NameId
WHERE rel.PTNameFk1 = xxx AND rel.PTRefFk1 = 7xxx
~~~
===
remove selfreferencing relationships
~~~ sql
DELETE RelName WHERE NameFk1 = NameFk2;
DELETE RelPTaxon WHERE PTNameFk1 = PTNameFk2 and PTRefFk1 = PTRefFk2
~~~