You need to check your rCTE. You're not using its capabilities and could simply use the table without CTE for your update. Read the examples that Chris and I posted...

Are you looking for something like this?

WITH rCTE AS (
   SELECT [NewLevel] = 1,
      ID,
      ParentID,
      [Name],
     ...

There seems to be something missing there, but I'm not sure what would it be without sample data. I wrote an article about a similar problem some time ago, it...

This might help you.
http://www.sqlservercentral.com/scripts/T-SQL/117890/

Just found the following code. This is what happens when a procedural developer starts writing T-SQL. Fortunately, some learn to stop the non-sense.

--Variables names changed

By the way, the execution plan attached is only for the following statement.
set statistics time, io on

In other words, it's useless.

I didn't check in detail, but something like this might improve the query.

SELECT CASE
   WHEN STATE = 'W. Iowa'
    THEN 'Iowa'
   ...