CTE Correct Syntax

Question

I am trying to UPDATE a CTE and getting an error: Invalid Object Name.

any explanation will be highly appreciated.

Thank You

 DECLARE @tbl AS TABLE (id int identity(1,1) PRIMARY KEY, string varchar(100));
    INSERT INTO @tbl VALUES
    ('JL & SONS Applications'),
    ('S&T Visions'),
    ('B & S Corporations'),
    ('A & C SHEET Aviation'),
    ('YOUNCE, Jane & Martin')
    ; 
 WITH CTE AS (
    SELECT id, string, charindex('&', string) as Location, 
           charindex(' ', string) as po
    FROM   @tbl
 )
 SELECT string, 
        LeftSideAnme = 
        CASE WHEN Location <= 5 THEN null 
             WHEN po > 0        THEN SUBSTRING(string,0,Location)
             ELSE string
             END,
        RightSideName = 
        CASE WHEN Location <= 5 THEN NULL
             WHEN po > 0        THEN SUBSTRING(string,Location+1,LEN(string)) 
             ELSE NULL
             END,
         Location
 FROM  CTE

 UPDATE CTE

Answer 1

I think that you should allocate the columns, for example:

DECLARE @tbl AS TABLE (id int identity(1,1) PRIMARY KEY, string varchar(100), LeftSideName varchar(max), RightSideName varchar(max))

INSERT INTO @tbl(string) VALUES
     ('JL & SONS Applications'),
     ('S&T Visions'),
     ('B & S Corporations'),
     ('A & C SHEET Aviation'),
     ('YOUNCE, Jane & Martin')
; 
WITH CTE AS (
   SELECT *, charindex('&', string) as Location, charindex(' ', string) as po
   FROM   @tbl
)
update CTE
set LeftSideName = 
       CASE WHEN Location <= 5 THEN null 
            WHEN po > 0        THEN SUBSTRING(string,0,Location)
            ELSE string
            END,
    RightSideName = 
       CASE WHEN Location <= 5 THEN NULL
            WHEN po > 0        THEN SUBSTRING(string,Location+1,LEN(string)) 
            ELSE NULL
            END

-- check the results
select * from @tbl

Answer 2

You asked why it would not work, so I'll answer just this question - the common table expression can only be used by the immediate statement that follows it. Think of it as a subquery. So you can not do select from cte and then update using that CTE. You can update CTE (which will update underlying table assuming that table has a primary key) for all rows which are selected from CTE.