question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked EchoLiu-msft commented

Convert Select To Update

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


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please have any updates?

Regards
Echo

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @InigoMontoya-1790,

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.




image.png (52.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The sixth line (“Jason – Joe”) does not seem valid.

0 Votes 0 ·

Thanks, I have revised the answer.

0 Votes 0 ·