Convert Select To Update

Question

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

Answer 1

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

Answer 2

Hi @Inigo Montoya ,

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.