Cleanup Multiple MAVIS records per AAPB GUID

GUIDCleanup.sql

-- Shows that there are 1819 titles with duplicated GUIDS
SELECT COUNT(title_no)
FROM aims.TITLE_OTHER_ID_FQV
WHERE identifier IN
  (SELECT identifier
  FROM
    (SELECT identifier,
      COUNT(title_no) AS tcount
    FROM aims.TITLE_OTHER_ID_FQV
    WHERE IDENTIFIER_TYPE = 'AMARCGUID'
    GROUP BY identifier
    ORDER BY tcount DESC
    )
  WHERE tcount >1
  );

-- gives the list of affected titles (titles that are records with shared AAPB GUIDs)
select unique(title_no) from aims.ITEM where TITLE_NO in (SELECT title_no
FROM aims.TITLE_OTHER_ID_FQV
WHERE identifier IN
  (SELECT identifier
  FROM
    (SELECT identifier,
      COUNT(title_no) AS tcount
    FROM aims.TITLE_OTHER_ID_FQV
    WHERE IDENTIFIER_TYPE = 'AMARCGUID'
    GROUP BY identifier
    ORDER BY tcount DESC
    )
  WHERE tcount >1
  ));