 concept_class_id like 'Device')
 and invalid_reason is null
 or concept_code in (select concept_code_1 from ingred_to_ingred_FINAL_BY_Lena where concept_code_2 is null) --"standard ingredient"
-;
---Supplier
-update drug_concept_stage 
-set concept_class_id = 'Supplier' where concept_class_id = 'Manufacturer'
+
   ;
   --add newly created ingredients
   insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)

 b.concept_code as concept_code_2, b.concept_name as concept_name_2, b.invalid_reason 
 from drug_concept_stage a 
 join drug_concept_stage b on a.concept_name like  '%('||b.concept_name||')%'
 where b.concept_class_id ='Manufacturer'
 and a.concept_class_id like 'Branded Drug%' and a.concept_code not in (select concept_code from non_drug_full)
 ;
 */
 --CLinical Drug to ingredients using existing relationship, later this relationship will be updated with ds_stage table
 drop table Clinical_to_Ingred;
 create table Clinical_to_Ingred as
select distinct a.concept_code as concept_code_1, a.concept_name as concept_name_1, cs.concept_code as concept_code_2, cs.concept_name as concept_name_2, cs.INSERT_ID 

join concept c on a.concept_code = c.concept_code and c.vocabulary_id = 'SNOMED' and a.concept_class_id like 'Branded Drug%'
 join concept_relationship r on r.concept_id_1 = c.concept_id 
 join concept d on r.concept_id_2 = d.concept_id and d.vocabulary_id = 'SNOMED'
join drug_concept_stage cs on cs.concept_code  = d.concept_code and cs.concept_class_id like 'Clinical Drug%' 
)
;
--duplicates due to ONLY DEPRECATED to NON-DEPRECATED DRUGS relationship??
DELETE from Branded_to_clinical a 
where exists (select 1 from 
(
