 --------------------------------------------------------
 
 -- UPSTREAM
-SELECT SNP_ID, GENE_ID, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE FROM (
+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);
+  AND INTERGENIC = 1;
 -- DOWNSTREAM
-SELECT SNP_ID, GENE_ID, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE FROM (
+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);
+  AND INTERGENIC = 1;
 -- INTERGENIC
-SELECT SNP_ID, GENE_ID, GENE_NAME, SNP_GENE_SYMBOLS, IS_INTERGENIC, IS_UPSTREAM, IS_DOWNSTREAM, DISTANCE FROM (
-  SELECT s.ID AS SNP_ID, g.ID AS GENE_ID, g.GENE AS GENE_NAME, gs.SNP_GENE_SYMBOLS, 1 AS IS_INTERGENIC, 0 AS IS_UPSTREAM, 0 AS IS_DOWNSTREAM, NULL AS DISTANCE
+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);
+  WHERE INTERGENIC = 0;
 
 --------------------------------------------------------
 -- Migrate data into ASSOCIATION_REPORT

-- Migrate data into SINGLE_NUCLEOTIDE_POLYMORPHISM
--------------------------------------------------------

UPDATE SINGLE_NUCLEOTIDE_POLYMORPHISM snp SET MERGED = (
  SELECT gs.MERGED
  FROM GWASSTUDIESSNP gs
  JOIN GWASSNPXREF sx ON sx.GWASSTUDIESSNPID = gs.ID
  JOIN GWASSNP s ON sx.SNPID = s.ID
  WHERE s.ID = snp.ID);


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

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