Update a CTE after a select statment

Question

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

Answer 1

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;

Answer 2

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

Answer 3

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

Answer 4

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