title: update sql getting select more than one row
body: <p>Following query is throwing the error <code>[SQL0811]</code></p>
  
  <p>It says that the result of the SELECT returns more than one row.  But when I run the queries individually, they return one row per key.</p>
  
  <p>Think I need to have another WHERE clause checking table 2 and table 1.  But how do I get visibility to table 2 outside of the SELECT and into the WHERE clause? </p>
  
  <p>I tried something like this before the EXISTS:</p>
  
  <pre><code>where a.key1 = b.key1 and a.key2 = b.key2 and a.key3  b.key3, 
  </code></pre>
  
  <p>But when I run that I get the error:</p>
  
  <blockquote>
    <p>column or global variable 'key1' not defined </p>
  </blockquote>
  
  <pre><code>UPDATE table 1 AS a
  SET (a.col1, a.col2, a.col3) = (
      SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
      FROM table 2 b
      LEFT OUTER JOIN table 1 c
      ON b.key1 = c.key1
        AND b.key2 = c.key2
        AND b.key3 = c.key3
  )
  WHERE (a.col1 = ' ' OR a.col2 = ' ' OR a.col3 = ' ')
    AND EXISTS(
          SELECT 1
          FROM table c trk
                   INNER JOIN table d sts ON trk.key1 = sts.key1
          WHERE trk.key1 = a.key1
            AND sts.status IN (' ', 'REQ')
      )
  </code></pre>