title: UPDATE not working properly with linked server
body: <p>Please see the query below:</p>
  
  <pre><code>select thisdate,created from dbusns d
  INNER JOIN (SELECT  MAX(created) created,
                      C.reference 
              FROM dbusns
              INNER JOIN [server].CUSTODYDB.DBO.dbcustody C
                  ON dbusnS.urns = C.reference
              WHERE dbusns.datasetname = 'CUSTODY'
              GROUP BY C.reference) T on D.urns = T.reference
  WHERE D.urns = '1' 
  </code></pre>
  
  <p>This returns: 2008-10-31 00:00:00.000, 2008-11-01 00:00:00.000</p>
  
  <p>I then run the query below:</p>
  
  <pre><code>UPDATE D
  SET thisdate = T.created
  FROM dbusns D
  INNER JOIN (SELECT  MAX(created) created,
                      C.reference 
              FROM dbusns
              INNER JOIN [server].CUSTODYDB.DBO.dbcustody C
                  ON dbusnS.urns = C.reference
              WHERE dbusns.datasetname = 'CUSTODY'
              GROUP BY C.reference) T on D.urns = T.reference
  WHERE D.urns = '1' 
  </code></pre>
  
  <p>I then run the query below, which returns: 2008-10-31 00:00:00.000 (it should be: 2008-11-01 00:00:00.000)</p>
  
  <pre><code>select THISDATE from dbusns where datasetname='CUSTODY' AND URNS='1'
  </code></pre>
  
  <p>Created and ThisDate are datetimes.  If I reduce the number of records in dbcustody to 29,598 then the value updates correctly.  I tried deleting the 29,598th record but this did not work.</p>
  
  <p>What is going on? It appears to be the execution plan that is incorrect as the remote query it is sending is:</p>
  
  <pre><code>SELECT TOP 1 created FROM dbcustody where reference='1' 
  </code></pre>
  
  <p>Why is not sending a MAX statement/ORDER BY created desc?</p>