 
 -- Replace concept_codes XXX123 with OMOP123
 -- Create replacement map
-drop table if exists xxx_replace;
-create table xxx_replace (
-  xxx_code varchar(50),
-  omop_code varchar(50)
-);
+DROP TABLE IF EXISTS xxx_replace;
+CREATE TABLE xxx_replace (
+	xxx_code VARCHAR(50),
+	omop_code VARCHAR(50)
+	);
 
 -- generate OMOP codes for new concepts
-insert into xxx_replace
-select concept_code as xxx_code, 'OMOP'||nextval('omop_seq') as omop_code
-from concept_stage 
-where concept_code like 'XXX%' 
-;
+INSERT INTO xxx_replace
+SELECT concept_code AS xxx_code,
+	'OMOP' || NEXTVAL('omop_seq') AS omop_code
+FROM concept_stage
+WHERE concept_code LIKE 'XXX%';
 
 -- replace concept_stage
-update concept_stage cs
-set concept_code=x.omop_code
-from xxx_replace x
-where cs.concept_code=x.xxx_code
-;
---!!!
+UPDATE concept_stage cs
+SET concept_code = x.omop_code
+FROM xxx_replace x
+WHERE cs.concept_code = x.xxx_code;
+
 -- replace concept_relationship_stage
-update concept_relationship_stage crs
-set concept_code_1=x.omop_code
-from xxx_replace x
-where crs.concept_code_1=x.xxx_code
-;
-
-update concept_relationship_stage crs
-set concept_code_2=x.omop_code
-from xxx_replace x
-where crs.concept_code_2=x.xxx_code
-;
+UPDATE concept_relationship_stage crs
+SET concept_code_1 = x.omop_code
+FROM xxx_replace x
+WHERE crs.concept_code_1 = x.xxx_code;
+
+UPDATE concept_relationship_stage crs
+SET concept_code_2 = x.omop_code
+FROM xxx_replace x
+WHERE crs.concept_code_2 = x.xxx_code;
 
 -- replace ingredients in drug_strength
-update drug_strength_stage ds
-set ingredient_concept_code=x.omop_code
-from xxx_replace x
-where ds.ingredient_concept_code=x.xxx_code;
+UPDATE drug_strength_stage ds
+SET ingredient_concept_code = x.omop_code
+FROM xxx_replace x
+WHERE ds.ingredient_concept_code = x.xxx_code;
 
 -- Remove negative and 0 concept_ids from concept_stage
-update concept_stage set concept_id=null;
-
---get duplicates for some reason 
-delete from concept_relationship_stage a where exists (
-  select 1 from  (
-    select concept_code_1,concept_code_2,relationship_id, max(ctid) as rid from concept_relationship_stage group by concept_code_1,concept_code_2,relationship_id having count(1)>1
-  ) x 
-  where a.concept_code_1= x.concept_code_1 and a.concept_code_2=x.concept_code_2 and a.relationship_id=x.relationship_id and x.rid=a.ctid
-);
+UPDATE concept_stage
+SET concept_id = NULL;
+
+--get duplicates for some reason
+DELETE
+FROM concept_relationship_stage crs
+WHERE EXISTS (
+		SELECT 1
+		FROM concept_relationship_stage crs_int
+		WHERE crs_int.concept_code_1 = crs.concept_code_1
+			AND crs_int.concept_code_2 = crs.concept_code_2
+			AND crs_int.vocabulary_id_1 = crs.vocabulary_id_1
+			AND crs_int.vocabulary_id_2 = crs.vocabulary_id_2
+			AND crs_int.relationship_id = crs.relationship_id
+			AND crs_int.ctid > crs.ctid
+		);
 
 -- Working with replacement mappings
 DO $_$

* See the License for the specific language governing permissions and
* limitations under the License.
* 
* Authors: Christian Reich
* Date: 2016-2017
**************************************************************************/

/***************************************************************************
To do list: 
- Possible excipient, 
- mEq to mmol, 
- other funny units, 
- use dfg_id for best pattern search
- don't use quantifieds for r_uds, use the equivalent non-quantified one, otherwise rounding error possible
***************************************************************************/


-- Auto-generate all names 
-- Create RxNorm-style units. UCUM units have no normalized abbreviation
DROP TABLE IF EXISTS rxnorm_unit;
CREATE UNLOGGED TABLE rxnorm_unit (
	rxn_unit VARCHAR(20),
	concept_id int4
	);

INSERT INTO rxnorm_unit VALUES 
 ('ORGANISMS', 45744815), -- Organisms, UCUM calls it {bacteria}
 ('%', 8554),
 ('ACTUAT', 45744809), -- actuation
