Convert Select To Update

Inigo Montoya 586 Reputation points
2021-04-23T14:06:59.697+00:00

Yesterday, I asked about writing a Select to concatenate some data, but now I need it to be an update.

  Drop Table #Test
  Drop Table #FullData

  Create Table #Test
  (
     name varchar(100)
  ,manager varchar(100)
  )

  Insert Into #Test Values ('Jason', 'Joe'), ('Mark', 'Joe'), ('Mitch', 'Mark'), ('Michael', 'Mark'), ('Jason', 'Mark'), ('Ezekial', 'Jason')

  Select name
  INTO #FullData
  from #Test

How can i convert this Select statement to be an UPDATE statement and update the field name in #FullData?

   Select Distinct
   (Select Stuff(
     (Select ' - ' + name From #Test t1 Where t.manager = t1.manager Order By t1.name 
     For XML Path(''),Type)
     .value('text()[1]','nvarchar(max)'),1,2,N'')) As Names
 From #Test t
 Where t.manager = 'Joe';

After update, my expected results would be

  Jason - Joe
  Mark - Joe
  Mitch - Mark
  Michael - Mark
  Jason - Mark
  Ezekial - Jason
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 82,646 Reputation points
    2021-04-23T16:15:22.993+00:00

    Try this converted statement:

    ;
    with Q1 as
    (
        select *, row_number() over(partition by name order by manager) as rn
        from #test
    ),
    Q2 as
    (
        select *, row_number() over(partition by name order by (select null)) as rn
        from #FullData
    )
    update Q2
    set name = concat( Q2.name, ' - ', Q1.manager)
    from Q2 
    inner join Q1 on Q1.name = Q2.name and Q1.rn = Q2.rn
    
    No comments

  2. EchoLiu-MSFT 14,416 Reputation points
    2021-04-26T05:48:47.06+00:00

    Hi @Inigo Montoya ,

    91191-image.png
    I checked your last post. The answer you got in the last question is to connect the names with the same manager with ‘-’. In the current problem, the desired result is to connect the name and the corresponding manager with ‘-’. These are two different problems, so after changing the select query you posted to update, it cannot get the results you expect.

    For the current issue, please refer to the following solution:

    Drop Table #Test  
    Drop Table #FullData  
      
    Create Table #Test(name varchar(100),manager varchar(100))  
    Insert Into #Test Values ('Jason', 'Joe'), ('Mark', 'Joe'),  
                             ('Mitch', 'Mark'), ('Michael', 'Mark'),   
      ('Jason', 'Mark'), ('Ezekial', 'Jason')  
          
    SELECT CONCAT_WS( ' - ',name, manager) as name  
    INTO #FullData  
    FROM #Test   
      
    SELECT * FROM  #FullData  
    

    Output:

    name  
    Jason - Joe  
    Mark - Joe  
    Mitch - Mark  
    Michael - Mark  
    Jason - Mark  
    Ezekial - Jason  
    

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.