C4hMVtd9

1--OMOP replacement: existing OMOP codes and shift sequence to after last code in devv5.concept
2drop table if exists code_replace
3;
4create table code_replace as
5select
6	d.concept_code as old_code,
7	c.concept_code as new_code
8from drug_concept_stage d 
9left join concept c on
10	c.vocabulary_id = d.vocabulary_id and
11	--c.invalid_reason is null and
12	c.concept_name = d.concept_name and
13	c.concept_class_id = d.concept_class_id
14where d.concept_code like 'OMOP%'
15;
16DO $$
17DECLARE
18	ex INTEGER;
19BEGIN
20	SELECT MAX(replace(concept_code, 'OMOP','')::int4)+1 into ex FROM devv5.concept WHERE concept_code like 'OMOP%'  and concept_code not like '% %';
21	DROP SEQUENCE IF EXISTS new_vocab;
22	EXECUTE 'CREATE SEQUENCE new_vocab INCREMENT BY 1 START WITH ' || ex || ' NO CYCLE CACHE 20';
23END $$
24;
25update code_replace
26set	new_code = 'OMOP' || nextval('new_vocab')
27where new_code is null
28;
29UPDATE drug_concept_stage a
30SET concept_code = b.new_code
31FROM code_replace b
32WHERE a.concept_code = b.old_code;
33
34UPDATE relationship_to_concept a
35SET concept_code_1 = b.new_code
36FROM code_replace b
37WHERE a.concept_code_1 = b.old_code;
38
39UPDATE ds_stage a
40SET ingredient_concept_code = b.new_code
41FROM code_replace b
42WHERE a.ingredient_concept_code = b.old_code;
43
44UPDATE ds_stage a
45SET drug_concept_code = b.new_code
46FROM code_replace b
47WHERE a.drug_concept_code = b.old_code;
48
49UPDATE internal_relationship_stage a
50SET concept_code_1 = b.new_code
51FROM code_replace b
52WHERE a.concept_code_1 = b.old_code;
53
54UPDATE internal_relationship_stage a
55SET concept_code_2 = b.new_code
56FROM code_replace b
57WHERE a.concept_code_2 = b.old_code;
58
59UPDATE pc_stage a
60SET drug_concept_code = b.new_code
61FROM code_replace b
62WHERE a.drug_concept_code = b.old_code
63;