I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.

Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.

The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.

The correct form of the UPDATE that you're trying to do should be as follows...

UPDATE tgt

SET primary_plan = 0

FROM dbo.eligibility tgt

JOIN dbo.temp_eligibility e

WHERE tgt.eligibility_id = e.eligibility_id

;