[SOLVED] Update with Sub Query Derived Table Error

Update with Sub Query Derived Table Error

I have the following SQL statement to simply update the #temp temp table with the latest package version number in our Sybase 15 database.

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

To me (mainly Oracle & SQL Server experience more than Sybase) there is little wrong with this statement. However, Sybase throws an exception:

You cannot use a derived table in the FROM clause of an UPDATE or DELETE statement.

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.

Any ideas or guidance would be much appreciated.

Solution

I guess this is a limitation of Sybase (not allowing derived tables) in the FROM clause of the UPDATE. Perhaps you can rewrite like this:

UPDATE t
SET t.versionId = l.versionId
FROM #temp t
  INNER JOIN 
    Group_Packages l
      ON t.packageId = l.packageId
WHERE
    l.groupId IN ( SELECT groupId 
                   FROM User_Group 
                   WHERE userXpId = 'someUser')
  AND
    l.versionId =
        ( SELECT MAX(gp.versionId)
          FROM Group_Packages gp 
          WHERE gp.groupId IN ( SELECT groupId 
                                FROM User_Group 
                                WHERE userXpId = 'someUser')
            AND gp.packageId = l.packageId
        ) ;