 -- Migrate data into SINGLE_NUCLEOTIDE_POLYMORPHISM
 --------------------------------------------------------
 
-UPDATE SINGLE_NUCLEOTIDE_POLYMORPHISM snp SET MERGED = (
-  SELECT gs.MERGED
+UPDATE SINGLE_NUCLEOTIDE_POLYMORPHISM snp SET (MERGED, FUNCTIONAL_CLASS) = (
+SELECT DISTINCT gs.MERGED, gs.FUNCTIONAL_CLASS
   FROM GWASSTUDIESSNP gs
   JOIN GWASSNPXREF sx ON sx.GWASSTUDIESSNPID = gs.ID
   JOIN GWASSNP s ON sx.SNPID = s.ID
-  WHERE s.ID = snp.ID);
-
+  WHERE MERGED IS NOT NULL
+  AND s.ID = snp.ID);

  LAST_UPDATE_DATE TIMESTAMP(6),
  GENE_ERROR NUMBER(19,0),
  PUBMED_ID_ERROR NUMBER(19,0),
  SNP_ERROR VARCHAR2(255 CHAR),
  SNP_GENE_ON_DIFF_CHR VARCHAR2(255 CHAR),
  NO_GENE_FOR_SYMBOL VARCHAR2(255 CHAR),
  GENE_NOT_ON_GENOME VARCHAR2(255 CHAR));

--------------------------------------------------------
--  DDL for Index STUDY_REP_ID_PK

--------------------------------------------------------

-- UPSTREAM
INSERT INTO SNP_MAPPED_GENE (SNP_ID, GENE_ID, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE)
  SELECT s.ID AS SNP_ID, g.ID AS GENE_ID, 0 AS IS_INTERGENIC, 1 AS IS_UPSTREAM, 0 AS IS_DOWNSTREAM, gs.UPSTREAM_GENE_DISTANCE AS DISTANCE
  FROM GWASSTUDIESSNP gs
  JOIN GWASSNPXREF sx ON sx.GWASSTUDIESSNPID = gs.ID
  JOIN GWASSNP s ON sx.SNPID = s.ID
  JOIN GWASGENE g ON g.GENE = gs.UPSTREAM_GENE_SYMBOL
  WHERE UPSTREAM_GENE_DISTANCE IS NOT NULL
  AND INTERGENIC = 1;
-- DOWNSTREAM
INSERT INTO SNP_MAPPED_GENE (SNP_ID, GENE_ID, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE)
  SELECT s.ID AS SNP_ID, g.ID AS GENE_ID, 0 AS IS_INTERGENIC, 0 AS IS_UPSTREAM, 1 AS IS_DOWNSTREAM, gs.DOWNSTREAM_GENE_DISTANCE AS DISTANCE
  FROM GWASSTUDIESSNP gs
  JOIN GWASSNPXREF sx ON sx.GWASSTUDIESSNPID = gs.ID
  JOIN GWASSNP s ON sx.SNPID = s.ID
  JOIN GWASGENE g ON g.GENE = gs.DOWNSTREAM_GENE_SYMBOL
  WHERE DOWNSTREAM_GENE_DISTANCE IS NOT NULL
  AND INTERGENIC = 1;
-- INTERGENIC
INSERT INTO SNP_MAPPED_GENE (SNP_ID, GENE_ID, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE)
  SELECT s.ID AS SNP_ID, g.ID AS GENE_ID, 1 AS IS_INTERGENIC, 0 AS IS_UPSTREAM, 0 AS IS_DOWNSTREAM, NULL AS DISTANCE
  FROM GWASSTUDIESSNP gs
  JOIN GWASSNPXREF sx ON sx.GWASSTUDIESSNPID = gs.ID
  JOIN GWASSNP s ON sx.SNPID = s.ID
  JOIN GWASGENE g ON g.GENE = gs.SNP_GENE_SYMBOLS
  WHERE INTERGENIC = 0;

--------------------------------------------------------
-- Migrate data into ASSOCIATION_REPORT
