 WHERE crs.concept_code_2 = i.concept_code_1
 	AND crs.vocabulary_id_2 = i.vocabulary_id_1;
 
---6. Update wrong Maps to Brand Names and one totally incorrect drug form
+--6. Update wrong 'Maps to' and relationships from Regimen to Drugs to Brand Names
+--and one totally incorrect drug form
 --to do, make this step automatic
 UPDATE concept_relationship_stage crs
-SET concept_code_2 = i.concept_code_2,
-	vocabulary_id_2 = i.vocabulary_id_2
+SET concept_code_2 = i.new_code,
+	vocabulary_id_2 = i.new_vocab
 FROM (
-	SELECT c1.concept_code concept_code_1,
-		c1.vocabulary_id vocabulary_id_1,
-		c2.concept_code concept_code_2,
-		c2.vocabulary_id vocabulary_id_2
-	FROM concept_relationship r
-	JOIN concept c1 ON c1.concept_id = r.concept_id_1
+	SELECT DISTINCT crs.concept_code_2 AS old_code,
+		crs.vocabulary_id_2 AS old_vocab,
+		c2.concept_code AS new_code,
+		c2.vocabulary_id AS new_vocab
+	FROM concept_relationship_stage crs
+	JOIN concept c1 ON c1.concept_code = crs.concept_code_2
+		AND c1.vocabulary_id = crs.vocabulary_id_2
 		AND c1.vocabulary_id = 'RxNorm'
 		AND c1.concept_class_id = 'Brand Name'
-	JOIN concept c2 ON c2.concept_id = r.concept_id_2
+	JOIN concept_relationship cr ON cr.concept_id_1 = c1.concept_id
+		AND cr.relationship_id = 'Brand name of'
+		AND cr.invalid_reason IS NULL
+	JOIN concept c2 ON c2.concept_id = cr.concept_id_2
 		AND c2.concept_class_id = 'Ingredient'
 		AND c2.standard_concept = 'S'
-	WHERE r.relationship_id = 'Brand name of'
-		AND r.invalid_reason IS NULL
-		AND EXISTS (
-			SELECT 1
-			FROM concept_relationship_stage crs_int
-			WHERE crs_int.concept_code_2 = c1.concept_code
-				AND crs_int.vocabulary_id_2 = c1.vocabulary_id
-				AND crs_int.relationship_id = 'Maps to'
-				-- fix this later
-				AND NOT EXISTS (
-					SELECT 1
-					FROM concept_stage cs
-					WHERE cs.concept_code = crs_int.concept_code_1
-						AND cs.vocabulary_id = crs_int.vocabulary_id_1
-						AND cs.concept_name LIKE '% and %'
-					)
+	JOIN concept_stage cs ON cs.concept_code = crs.concept_code_1
+		AND cs.concept_name NOT LIKE '% and %' -- avoiding the combinatory drugs, they are mapped manually, see union below
+		AND c1.concept_code NOT IN (
+			'2119715',
+			'1927886'
+			) -- Herceptin Hylecta , Rituxan Hycela -  need to make better automatic work-aroud when have time
+	WHERE crs.relationship_id IN (
+			'Maps to',
+			'Has antineopl Rx',
+			'Has immunosuppr Rx',
+			'Has local therap Rx',
+			'Has support med Rx'
 			)
 	--Clinical Drug Forms Picked up manually
 	

				'BioCondition'
				)
			THEN 'Condition'
		ELSE 'Undefined'
		END AS domain_id,
	h.vocabulary_id,
	CASE 
		WHEN h.concept_class_id = 'Brand_Name'
			THEN 'Brand Name'
		ELSE h.concept_class_id
		END AS concept_class_id,
	CASE 
		WHEN h.concept_class_id = 'Component Class'
			THEN 'C'
		ELSE h.standard_concept
		END AS standard_concept,
	h.concept_code,
	h.valid_start_date,
	h.valid_end_date,
	NULL AS invalid_reason --fix when new release comes up
FROM sources.hemonc_cs h
WHERE h.concept_class_id IN (
		'Regimen type', -- type

	) i -- in order not to write the relationship_id case in the last condition, I use subquery
WHERE (
		i.concept_code_1,
		i.vocabulary_id_1,
		i.relationship_id,
		i.concept_code_2,
		i.vocabulary_id_2
		) NOT IN (
		SELECT concept_code_1,
			vocabulary_id_1,
			relationship_id,
			concept_code_2,
			vocabulary_id_2
		FROM concept_relationship_stage
		);

