 
 --31.5 delete precise ingredients
 DELETE relationship_to_concept
-WHERE concept_code_1 in ('1371041','236340');+WHERE concept_code_1 in ('1371041','236340');
+
+--32 Rename RxE to RxO
+INSERT INTO vocabulary (vocabulary_id, vocabulary_name, vocabulary_concept_id)
+     VALUES ('RxO', 'RxO', 100000);
+
+UPDATE concept
+SET vocabulary_id = 'RxO' 
+WHERE vocabulary_id = 'RxNorm Extension';
+
+UPDATE concept_relationship
+SET invalid_reason = 'D'
+WHERE concept_id_1 IN (SELECT concept_id_1 FROM concept_relationship JOIN concept ON concept_id_1 = concept_id AND vocabulary_id = 'RxO')
+OR concept_id_2 IN (SELECT concept_id_2 FROM concept_relationship JOIN concept ON concept_id_2 = concept_id AND vocabulary_id = 'RxO');     

                              JOIN ds_stage ON concept_code = drug_concept_code
                            WHERE REGEXP_LIKE (concept_name,'((Nitrous Oxide)|(Xenon)|(Isoflurane)|(Oxygen)) (\d+\.)?(\d+\s((MG/MG)|(ML/ML)))') AND numerator_unit != '%')
;

--16 Delete 3 legged dogs
DELETE FROM ds_stage

         )
UNION ALL
--Get RxNorm pack components from RxNorm
SELECT c2.concept_name
       'Rxfix',
       'Drug Product',
       NULL,
       c2.concept_code,
       NULL,
       c2.domain_id,
       c2.valid_start_date,
       c2.valid_end_date,
       c2.invalid_reason,
       c2.concept_class_id
   FROM pack_content pc
       JOIN concept c ON c.concept_id = pc.pack_concept_id AND c.vocabulary_id = 'RxNorm Extension'
       JOIN concept c2 ON c2.concept_id = pc.drug_concept_id AND c2.vocabulary_id = 'RxNorm'
       ;
COMMIT;

--4.2 Get upgraded Dose Forms, Brand Names, Supplier
