Why use UPDATE at all? Or use a function? This is possible to do in a single query.

And still have some options to include or exclude "non-valid" members!

SELECTCASE WHEN u.theIndex = 0 THEN u.StaffName ELSE '' END AS StaffName,

MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN u.ReviewDate ELSE '' END) AS [2006_Dates],

MAX(CASE WHEN u.ReviewDate LIKE '2007%' THEN u.ReviewDate ELSE '' END) AS [2007_Dates],

MAX(CASE WHEN u.ReviewDate LIKE '2008%' THEN u.ReviewDate ELSE '' END) AS [2008_Dates]

FROM(

SELECTt1.StaffName,

CONVERT(CHAR(10), t1.ReviewDate, 120) AS ReviewDate,

(

SELECTCOUNT(*)

FROM#TestData AS t2

WHEREt2.StaffName = t1.StaffName

AND YEAR(t2.ReviewDate) = YEAR(t1.ReviewDate)

AND t2.ReviewDate < t1.ReviewDate

AND t2.ReviewDate >= '20060101'

AND t2.ReviewDate < '20090101'

) AS theIndex

FROM#TestData AS t1

/* Remove this comment to only display staff who has dates in valid range

WHEREt1.ReviewDate >= '20060101'

AND t1.ReviewDate < '20090101'

*/

) AS u

GROUP BYu.StaffName,

u.theIndex

ORDER BYu.StaffName,

u.theIndexYou have also the option to change AND t2.ReviewDate < t1.ReviewDate to AND t2.ReviewDate > t1.ReviewDate if you want the dates sorted ascending instead.