This was a head scratcher. I think that because you used a table variable in your example, you need to use an alias so that sql can make the link back to the table to be updated i.e. we need to mimic the line

'AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID' whereas your line was 'AND pid = so.pid' which sent the query to find multiple values in the subquery like Grant says. I hope that makes sense.

UPDATE A

SET Sales = Sales +

(SELECT SUM(so.amt)

FROM @salesHeader AS so

WHERE so.sDate = (SELECT MAX(sDate)

FROM @salesHeader AS so2

WHERE so2.pid = so.pid)

AND A.pid = so.pid

GROUP BY so.pid )

from @salesperson as A