Delete Phantom Citations – Query #delete #citations #phantom

This query deletes from the CitationsTable those records that have no associated record in the SourceTable. These ‘phantom’ sources or ‘orphaned’ citations manifest themselves in the Edit Person screen as citations that return nothing when opened, are counted in the File > Properties report and may have odd effect in other reports.

The query is believed to be quite safe to run as it affects nothing dependent on having the proprietary RMNOCASE collation.

-- DeleteUnsourcedCitations.sql
-- 2010-01-29 ve3meo
-- Deletes Citations having lost their Source
 
DELETE FROM CitationTable
  WHERE CitationID IN
   (SELECT CitationID FROM CitationTable c
      LEFT JOIN SourceTable s ON c.SourceID=s.SourceID
      WHERE s.SourceID ISNULL);