8k row overflow error when updating row of size 5k
The update fails for broadly similar reasons to those I explained in answer to your previous question.

In this case, because you are potentially updating multiple rows where a key column of a unique index is changed*, SQL Server builds a plan that includes Split, Sort, and Collapse operators to avoid intermediate unique key violations (see this article for details).

The Sort operator thus introduced encounters an intermediate row (including internal overheads) of a width that exceeds the limit, so an error is raised. Adding an OPTION (ROBUST PLAN) hint to the update query shows this is unavoidable:


  Msg 8619, Level 16, State 2, Line 681
  The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. Resubmit your query without the ROBUST PLAN hint.


The source/target data relationships are unclear to me from a brief look, but if you can guarantee that each update operation will affect at most one row, you can avoid the need for the Split/Sort/Collapse by adding TOP (1) to the update statement:

UPDATE TOP (1) [TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET] 
SET ...


This is a bit of a hack, though. Ideally, the update statement construction and indexes should provide enough information to the optimizer so it can see that at most one row will be updated. In particular, it is best practice to write update statements that are deterministic.

Given the odd design and lack of clarity in the question, I'm not even go to try to decipher the data relationships, or query &amp; index changes that would be necessary to achieve this in detail.

* As Martin Smith pointed out in a comment, this would not be a problem in this particular situation if the table were not partitioned. Where the update sets the key to the same deterministic value in every row, Split/Sort/Collapse is not required, unless the table is also partitioned on that key. So, an alternative solution for this query is to not partition the table on sampletime.