title: How to update a column using values from another table in Amazon Redshift
body: <p>I have 2 tables:</p>
  
  <p>Table 'A':</p>
  
  <pre><code>Old_eid    new_eid
  &lt;null&gt;     &lt;null&gt;
  a          &lt;null&gt;
  b          &lt;null&gt;
  c          &lt;null&gt;
  </code></pre>
  
  <p>Table 'B':</p>
  
  <pre><code>eid1    eid2
  a         d
  b         e
  c         f
  </code></pre>
  
  <p>I would like to update Table 'A' as follows:</p>
  
  <pre><code>Old_eid    new_eid
  &lt;null&gt;     &lt;null&gt;
  a             d
  b             e
  c             f
  </code></pre>
  
  <p>I came up with the following query but it gives me an error:</p>
  
  <pre><code>UPDATE A
     SET new_eid = (SELECT eid2
                    FROM A a
                      JOIN B b ON a.old_eid = b.eid1)
  WHERE old_eid IS NOT NULL
  </code></pre>
  
  <p>But it gives me the following error:</p>
  
  <pre><code>UPDATE A
         SET new_eid = (SELECT eid2
                        FROM A a
                          JOIN B b ON a.old_eid = b.eid1)
      WHERE old_eid IS NOT NULL
  
  [Amazon](500310) Invalid operation: Invalid Query: 
  Details: 
   -----------------------------------------------
    error:  Invalid Query: 
    code:      8001
    context:   single-row subquery returns more than one row
    query:     967978
    location:  8.cpp:78
    process:   padbmaster [pid=15160]
    -----------------------------------------------;
  
  Execution time: 0.35s
  1 statement failed.
  </code></pre>
  
  <p>I can understand the error because it is resulting in more than one rows, but I'm not sure how to get what I want.</p>
  
  <p>How do I replace these values? Any help would be much appreciated.</p>