“Scrubbing Names via SQL Query/Batch” ( Simple Solution to Use CTEs in One Run)

Posted: January 8, 2018 | Author: | Filed under: Uncategorized | Leave a comment

Here is one solution posted on stackexchange:

https://dba.stackexchange.com/questions/11719/scrubbing-names-via-sql-query-batch

But you can use the two CTEs to update the table without using a temp table.

  

ALTER TABLE [mytable] DISABLE TRIGGER ALL
;with mycte as (

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid],[firstname],[lastname]	      
  FROM  [dbo].[mytable] e  
   WHERE  ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  )
  ,mycte1 as 
  ( 
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid] ,[firstname] ,[lastname]  FROM  [dbo].[mytable] 
 WHERE   ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  ) 
  Merge mycte1 e using mycte m on e.ID=m.ID
  WHEN  matched then 
  update
Set  e.FirstName = m.FirstName, 
e.LastName = m.LastName ;

ALTER TABLE [mytable] ENABLE TRIGGER ALL