title: Fill a table with elements from another table
body: <p>I've a problem with my java project. I have two text files to insert in postgresql with the jdbc driver. the two files are of this type:
  1) snp (rsid, chr, boolean value)
  2) locus (rsid, mrna, gene, class)</p>
  
  <p>I have to get two tables of this type:
  1)Snp (id serial, rsid varchar, chr varchar, has_sig boolean)
  2)Locus (id serial, mana_acc varchar, gene varchar, class varchar, snp_id     integer)
  where snp_id of locus is foreign key references to Snp(id).</p>
  
  <p>The field I can use for joining between tables is rsid which is common to both files.</p>
  
  <p>I've created the table in this way:</p>
  
  <pre><code>st = connection.prepareStatement(
                  "CREATE TABLE IF NOT EXISTS snp ("
                  + "id SERIAL PRIMARY KEY, "
                  + "rsid varchar(100), "
                  + "chr varchar(100), "
                  + "has_sig varchar(100))");
          st.executeUpdate();
          st.close();
  
          st = connection.prepareStatement(
                  "CREATE TABLE IF NOT EXISTS locus ("
                  + "id SERIAL PRIMARY KEY, "
                  + "rsid varchar(100), "
                  + "mrna_acc varchar(100), "
                  + "gene varchar(100), "
                  + "class varchar(100), "
                  + "snp_id integer REFERENCES snp(id) on delete cascade on update cascade)");
          st.executeUpdate();
          st.close();
  </code></pre>
  
  <p>Then I put the files in the tables using the select query instead of the snp_id field:</p>
  
  <pre><code>FileSnp fs = new FileSnp("/Users/valentinafratini/Documents/Progetto Tesi/FactoryMethodDb/snp.csv");
          fs.readFile();
          while (fs.line!=null) {
              fs.line = fs.reader.readLine();
  
              if (fs.line!=null &amp;&amp; fs.line.length()&gt;0) {
                      fs.obj = fs.line.split("\\s+");
                      fs.readSingleObj();
  
                      st = connection.prepareStatement("INSERT INTO snp ("
                          + "id, "
                          + "rsid, "
                          + "chr, "
                          + "has_sig) "
                          + "VALUES (DEFAULT, ?, ?, ?)");
                      st.setString(1, fs.rsid);
                      st.setString(2, fs.chr);
                      st.setString(3, fs.has_sig);
                      st.executeUpdate(); 
                      st.close();
  
  FileLocus fl = new FileLocus("/Users/valentinafratini/Documents/Progetto Tesi/FactoryMethodDb/locus.csv");
          fl.readFile();
          while (fl.line!=null) {
              fl.line = fl.reader.readLine();
  
              if (fl.line!=null &amp;&amp; fl.line.length()&gt;0) {
                      fl.obj = fl.line.split("\\s+");
                      fl.readSingleObj();
  
                      st = connection.prepareStatement("INSERT INTO locus ("
                          + "id, "
                          + "rsid, "  
                          + "mrna_acc, "
                          + "gene, "
                          + "class,"
                          + "snp_id) "
                          + "VALUES (DEFAULT, ?, ?, ?, ?, (SELECT id FROM snp s WHERE rsid = s.rsid))");
                      st.setString(1, fl.rsid);
                      st.setString(2, fl.mrna_acc);
                      st.setString(3, fl.gene);
                      st.setString(4, fl.classe);
                      st.executeUpdate();
                      st.close();
  </code></pre>
  
  <p>But when I fill in I have the following error:</p>
  
  <blockquote>
    <p>ERROR: more than one row returned by a subquery used as an expression</p>
  </blockquote>
  
  <p>Can you help me? 
  Thank you very much.</p>