title: UPDATE using INNER JOIN on SUBQUERY not working as expected
body: <p>I am trying to UPDATE col1 in tableA taking data from tableD using this query (prototype):</p>
  
  <pre><code>UPDATE tableA
  SET TableA.col1 = tableB.col1
  FROM tableA 
  INNER JOIN
      (SELECT tableC.col1, id 
       FROM
           (SELECT 
                ROW_NUMBER() OVER(PARTITION BY tableD.col1 ORDER BY (tableD.someCol desc) AS "Row Number", 
                col1, id 
            FROM tableD) tableC
       WHERE [Row Number] = 1) tableB ON tableA.id = tableB.id
  WHERE 
      some_clause_on_tableA_and_tableB;
  </code></pre>
  
  <p>But unfortunately this doesn't works properly (incorrect updates), but if I explicitly store data of tableB in a #temp table and then try to update tableA from that #temp table then it works fine (prototype query):</p>
  
  <pre><code>SELECT * 
  INTO #temp 
  FROM
      (SELECT 
           ROW_NUMBER() OVER(PARTITION BY tableD.col1 ORDER BY (tableD.someCol desc) AS "Row Number", 
           col1, id 
       FROM tableD) tableC
  WHERE
      [Row Number]=1;
  
  UPDATE tableA
  SET TableA.col1 = tableB.col1
  FROM tableA 
  INNER JOIN #temp tableB ON tableA.id = tableB.id
  WHERE some_clause_on_tableA_and_tableB;
  </code></pre>
  
  <p>I am not sure why the later works but first doesn't. It seems that in first query the two nested subqueries (with row ranking) are not able to fetch correct data but I may be wrong. Any help would be greatly appreciated!!!</p>
  
  <p>Thanks,
  Sachin</p>