title: Copy values from one table to another based on column comparison
body: <p>I have a Question and Answer table in SQL Server 2008 R2. </p>
  
  <p><strong>Question:</strong></p>
  
  <pre><code>q_id
  question_text
  </code></pre>
  
  <p><strong>Answer:</strong></p>
  
  <pre><code>a_id
  question_text
  answer_text
  </code></pre>
  
  <p>This is obviously bad design. The question_text field is duplicated in the <code>Question</code> and <code>Answer</code> tables and there is no foreign key relationship between <code>Answer</code> and <code>Question</code>. I want to create this relation by finding the <code>Question</code> (the <code>q_id</code>) for each <code>Answer</code> that is already present in the <code>Answer</code> table. I would do this by matching the <code>question_text</code> value that is copied to the <code>Answer</code> table to the rows in the <code>Question</code> table.</p>
  
  <p>This is what I'd like to do in pseudo code.</p>
  
  <ul>
  <li>create a nullable <code>q_id</code> field in the <code>Answer</code> table (nullable because there is already data in the <code>Answer</code> table)</li>
  <li>select everything from the <code>Answer</code> table.</li>
  <li>for each row in the result
  -- select the corresponding question from the <code>Question</code> table by comparing the <code>question_text</code> value.
  -- insert the <code>q_id</code> into the nullable <code>q_id</code> column in the <code>Answer</code> table</li>
  <li>change the <code>q_id</code> column in the <code>Answer</code> to not nullable.</li>
  </ul>
  
  <p>Could you help me achieve this in SQL Server 2008 R2? Thanks.</p>
  
  <p>PS: All <code>question_text</code> values in the <code>Question</code> table are unique.</p>