Update a CTE after a select statment

Debilon 431 Reputation points
2022-03-23T22:07:16.993+00:00

So i finally got my CTE to work and get me the correct info
but how do i update it when it ends with a select statement ? some documentation about CTE's will be highly appreciated at this point.

 ;WITH CTE AS
 (SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY CHARINDEX(Name1,name)) AS RNum
  FROM OwnerNames
  CROSS APPLY STRING_SPLIT(Name,' ')
 )
 SELECT id,Name,[1]AS LastName,
        CASE WHEN [3] IS NULL THEN NULL ELSE [2] END AS FirstName,
        CASE WHEN [3] IS NULL THEN [2] ELSE [3] END AS MiddleName

 FROM CTE

 PIVOT(MAX(value) FOR RNum IN([1],[2],[3],[4]))P
 where name is not null
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-03-24T13:22:50.063+00:00

    If direct update using CTE not working, you can use merge command using CTE2 as your source, e.g.

    ;MERGE OwnerNames as trgt
    using cte2 as srce on trgt.Id = srce.Id
    when matched and trgt.Name is not null then update
    set FirstName = srce.FirstName,
    LastName = srce.LastName
    MiddleInitial = srce.MiddleInitial;
    

3 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-23T22:27:23.28+00:00

    The ORDER BY in ROW_NUMBER() function looks a bit strange. I assume name1 is another column? If yes, order will be not determenistic.

    Anyway, here is documentation (you may be interested in the example F):

    https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

    0 comments No comments

  2. Naomi 7,361 Reputation points
    2022-03-23T22:31:59.517+00:00

    Assuming the original table has FirstName, LastName, MiddleInitial columns and you want to populate those:

    ;WITH CTE AS
      (SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY CHARINDEX(VALUE,name)) AS RNum
       FROM OwnerNames
       CROSS APPLY STRING_SPLIT(Name,' ')
      ),
      cte2 as (SELECT id,Name, [1] AS newLastName, 
             CASE WHEN [3] IS NULL THEN NULL ELSE [2] END AS newFirstName,
             CASE WHEN [3] IS NULL THEN [2] ELSE [3] END AS newMiddleName,
              FirstName, LastName, Middlename -- original columns from the table  
      FROM CTE
    
      PIVOT(MAX(value) FOR RNum IN([1],[2],[3],[4]))P
      where name is not null)
    
    UPDATE cte2 set FirstName = newFirstName, LastName = newLastName, MiddleName = newMiddleName;
    
    select * from OwnerNames where Name is not null -- verify the result of the update
    

  3. LiHong-MSFT 10,046 Reputation points
    2022-03-24T03:14:20.403+00:00

    Hi @Debilon
    If there are only id and string columns in your table, and the columns you want to update are FirstName, SecondName and ThirdName.
    The first thing you need to do is add three columns to the original table.

    ALTER TABLE your_Table_Name ADD LastName VARCHAR(30),  
                                    FirstName VARCHAR(30),  
    								MiddleName VARCHAR(30);  
    

    Then use multiple CTE, put the outer select statement into a new CTE2, and UPDATE CTE2 SET FirstName = newFirstName...

    ;WITH CTE AS  
    (  
     SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY CHARINDEX(Name1,name)) AS RNum  
     FROM OwnerNames  
     CROSS APPLY STRING_SPLIT(Name,' ')  
    ),  
    CTE2 AS  
    (  
     SELECT id,Name,[1]AS New_LastName,  
            CASE WHEN [3] IS NULL THEN NULL ELSE [2] END AS New_FirstName,  
            CASE WHEN [3] IS NULL THEN [2] ELSE [3] END AS New_MiddleName  
     FROM CTE  
     PIVOT(MAX(value) FOR RNum IN([1],[2],[3]))P  
     where name is not null  
    )  
    UPDATE CTE2 SET FirstName = New_FirstName,   
                    LastName = New_LastName,   
    				MiddleName = New_MiddleName;  
    

    If the original table already has these three columns, then skip the ADD COLUMN step, just update CTE2.

    For more details about Multiple CTE, please refer to this article: SQL Multiple CTE Syntax and T-SQL CTE Example
    Also check this article for more examples: CTE With (INSERT/ DELETE/ UPDATE) Statement In SQL Server

    Best regards,
    LiHong