  create sequence new_seq increment by 1 start with 1 nocycle cache 20 noorder;
 ;
 --add OMOP codes to new ingredients
-update ingred_to_ingred_FINAL_BY_Lena
+update ingr_to_ingr
 set CONCEPT_CODE_2 ='OMOP'||new_seq.nextval where CONCEPT_CODE_2 is null and CONCEPT_name_2 is not null
 ;
-insert into ingred_to_ingred_FINAL_BY_Lena (CONCEPT_CODE_1, CONCEPT_name_1)
-select distinct CONCEPT_CODE_2, CONCEPT_name_2 from ingred_to_ingred_FINAL_BY_Lena where CONCEPT_CODE_2 like 'OMOP%'
+insert into ingr_to_ingr (CONCEPT_CODE_1, CONCEPT_name_1)
+select distinct CONCEPT_CODE_2, CONCEPT_name_2 from ingr_to_ingr where CONCEPT_CODE_2 like 'OMOP%'
 ;
 
 --Non drug definition - several steps including different criteria for non-drug definition	

  end;
end;
/
 ;
drop table code_replace;
 create table code_replace as 
 select 'OMOP'||new_vocab.nextval as new_code, concept_code as old_code from (
select distinct  concept_code from drug_concept_stage where concept_code like 'OMOP%' order by (cast ( regexp_substr( concept_code, '\d+') as int))
)
;
update drug_concept_stage a set concept_code = (select new_code from code_replace b where a.concept_code = b.old_code) 
where a.concept_code like 'OMOP%'
; 

;
/*
--work with manual table 
drop table ingr_to_ingr;
create table ingr_to_ingr (CONCEPT_CODE_1	varchar (200),
CONCEPT_NAME_1 varchar (200),	INSERT_ID_1 int,  CONCEPT_CODE_2 varchar (200),	CONCEPT_NAME_2 varchar (200),	INSERT_ID_2 int , REL_TYPE varchar (20),	INVALID_REASON varchar (20))
;
WbImport -file=C:/mappings/DM+D/ingred_to_ingred_FIN_Lena.txt
         -type=text
         -table=ingr_to_ingr
         -encoding="ISO-8859-15"
         -header=true
         -decode=false
