CTE Correct Syntax

Debilon 431 Reputation points
2022-03-21T18:31:16.27+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2022-03-21T18:39:02.033+00:00

    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
    

1 additional answer

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-21T19:20:30.69+00:00

    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.