Re: Help with an Update Statment

Here is one way (SQL Server 2005):

WITH RankedOfficers
AS
(SELECT custID, OfficerSID,
ROW_NUMBER() OVER(PARTITION BY custID
ORDER BY OfficerRank) AS seq
FROM Co),
PivotOfficers
AS
(SELECT custID, [1] AS officer1, [2] AS officer2
FROM RankedOfficers
PIVOT (MAX(OfficerSID) FOR seq IN ([1], [2])) AS P),
UpdateCTE
AS
(SELECT OfficerSID1, OfficerSID2, officer1, officer2
FROM tbl_Output AS O
JOIN PivotOfficers AS P
ON O.custID = P.custID)
UPDATE UpdateCTE
SET OfficerSID1 = officer1,
OfficerSID2 = officer2;

HTH,

Plamen Ratchev
http://www.SQLStudio.com
.