delete.sql

CREATE temporary table if not exists ass_rel_ids as (
	SELECT rel.id
	FROM assessments AS obj
	JOIN relationships AS rel ON (
		(rel.source_type="Assessment" AND
		rel.source_id=obj.id AND
		rel.destination_type NOT IN ("Audit", "Comment", "OrgGroup", "Section", "AccessGroup", "System", "Contract", "DataAsset", "Regulation", "Snapshot", "Threat", "Objective", "Document", "Control", "Product", "Vendor", "Risk", "Facility", "Process", "Clause", "Standard", "Policy", "Person", "Market")) OR
		(rel.destination_type="Assessment" AND
		rel.destination_id=obj.id AND
		rel.source_type NOT IN ("Audit", "Comment", "OrgGroup", "Section", "AccessGroup", "System", "Contract", "DataAsset", "Regulation", "Snapshot", "Threat", "Objective", "Document", "Control", "Product", "Vendor", "Risk", "Facility", "Process", "Clause", "Standard", "Policy", "Person", "Market")))
	ORDER BY obj.id
);

delete from relationships where id in (select * from ass_rel_ids);


CREATE temporary table if not exists issue_rel_ids as (
	SELECT rel.id
	FROM issues AS obj
	JOIN relationships AS rel ON (
		(rel.source_type="Issue" AND
		rel.source_id=obj.id AND
		rel.destination_type NOT IN ("Audit","Comment","OrgGroup","Section","AccessGroup","System","Contract","DataAsset","Regulation","Snapshot","Threat","Objective","Document","Control","Product","Vendor","Risk","Facility","Process","Clause","Standard","Policy","Person","Market")) OR
		(rel.destination_type="Issue" AND
		rel.destination_id=obj.id AND
		rel.source_type NOT IN ("Audit","Comment","OrgGroup","Section","AccessGroup","System","Contract","DataAsset","Regulation","Snapshot","Threat","Objective","Document","Control","Product","Vendor","Risk","Facility","Process","Clause","Standard","Policy","Person","Market")))
	ORDER BY obj.id
);
  

delete from relationships where id in (select * from issue_rel_ids);