title: Update with Sub Query Derived Table Error
body: <p>I have the following SQL statement to simply update the <code>#temp</code> temp table with the latest package version number in our Sybase 15 database.</p>
  
  <pre><code>UPDATE t
  SET versionId = l.latestVersion
  FROM #temp t INNER JOIN (SELECT gp.packageId
                                  , MAX(gp.versionId) latestVersion
                           FROM Group_Packages gp 
                           WHERE gp.groupId IN (SELECT groupId 
                                                FROM User_Group 
                                                WHERE userXpId = 'someUser')
                           GROUP BY gp.packageId) l
  ON t.packageId = l.packageId
  </code></pre>
  
  <p>To me (mainly Oracle &amp; SQL Server experience more than Sybase) there is little wrong with this statement. However, Sybase throws an exception:</p>
  
  <pre><code>You cannot use a derived table in the FROM clause of an UPDATE or DELETE statement.
  </code></pre>
  
  <p>Now, I don't get what the problem is here. I assume it is because of the aggregation / GROUP BY being used. Of course, I could put the sub query in a temp table and join on it but I really want to know what the 'correct' method should be and what the hell is wrong.</p>
  
  <p>Any ideas or guidance would be much appreciated.</p>