RIGHT() works in a SELECT but not in an UPDATE?
why is it failing in the UPDATE but not the SELECT...FROM?


Because in your SELECT you are filtering out the rows via WHERE #ReleaseTemp.ReleaseNotes &lt;&gt; ''. You do not have that filter on your UPDATE statement and you probably have at least one row that has a length of 0 or 1. Try making it:

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2))
WHERE LEN(#ReleaseTemp.ReleaseNotes) &gt; 1;


Rather than checking for &lt;&gt; '', I changed the filter to ensure that the length of the string is at least 2 characters. This avoids the situation in which the string has only one character which does not equate to an empty string '' but would still get that error when passing in a -1 (from length of 1 minus 2) to the RIGHT function.

Please note that I included the LTRIM() which you currently have as a second step in order to avoid that second DML operation.