CODEWITHSUNDEEP
sql-server
Youssef
Youssef

Reputation: 50

Replacement for UPDATE statement with ORDER BY clause

I am having a hard time trying to execute an update query that should contain ORDER BY clause, but I'm unable to find a proper solution yet.

UPDATE I
SET RefItemID = AQ.ID,
    I.MagParamNum = AQ.MagParamNum
FROM SRO_VT_SHARD.._Items I
JOIN SRO_VT_SHARD.._Inventory INV ON INV.ItemID = I.ID64
JOIN SRO_VT_SHARD.._RefObjCommon ROC ON ROC.ID = I.RefItemID
JOIN _AEQItems AQ ON AQ.TypeID3 = ROC.TypeID3
                  AND AQ.TypeID4 = ROC.TypeID4
WHERE
    INV.Slot BETWEEN 0 AND 13 
    AND INV.Slot != 8 
    AND AQ.ReqLevel1 <= @Data2 
    AND INV.CharID = @CharID
ORDER BY AQ.ReqLevel1 DESC

Basically my query should work this way if ORDER BY clause is usable inside an update statement, but it doesn't. Is there something I can do which should solve this?

Thanks a lot in advance.

Upvotes: 0

Views: 94

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

You need to determine the exact row to update for each TypeID3 / TypeID4 combination, and you can't do that in the outer query. You may need to add additional ORDER BY clauses here to break ties. You may also want to specify only a subset of columns if you have an index that covers the columns in _AEQItems used to search and the columns you're updating.

;WITH AQ AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY TypeID3, TypeID4 ORDER BY ReqLevel1 DESC)
  FROM _AEQItems
)
UPDATE I
SET RefItemID   = AQ.ID,
    MagParamNum = AQ.MagParamNum
FROM SRO_VT_SHARD.._Items I
JOIN SRO_VT_SHARD.._Inventory INV ON INV.ItemID = I.ID64
JOIN SRO_VT_SHARD.._RefObjCommon ROC ON ROC.ID = I.RefItemID
JOIN AQ ON AQ.TypeID3 = ROC.TypeID3 AND AQ.TypeID4 = ROC.TypeID4
WHERE AQ.rn = 1 
  AND INV.Slot BETWEEN 0 AND 13 
  AND INV.Slot!=8 
  AND AQ.ReqLevel1 <= @Data2 
  AND INV.CharID = @CharID;

Upvotes: 4

fnightangel
fnightangel

Reputation: 426

Use a subquery:

    UPDATE I
SET RefItemID=AQ.ID,I.MagParamNum=AQ.MagParamNum
FROM SRO_VT_SHARD.._Items I
JOIN SRO_VT_SHARD.._Inventory INV ON INV.ItemID=I.ID64
JOIN SRO_VT_SHARD.._RefObjCommon ROC ON ROC.ID=I.RefItemID
JOIN (SELECT TypeID3, TypeID4, MAX(ReqLevel1) AS ReqLevel1 FROM _AEQItems GROUP BY TypeID3, TypeID4) AQ 
    ON AQ.TypeID3=ROC.TypeID3 
    AND AQ.TypeID4=ROC.TypeID4
WHERE INV.Slot BETWEEN 0 AND 13 AND INV.Slot!=8 AND AQ.ReqLevel1<=@Data2 AND INV.CharID=@CharID

Upvotes: 2

Related Questions