CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-update
Bobcat88
Bobcat88

Reputation: 758

SQL Update statement using multiple values

I want to update a table called Dash, the column I want to update is called Analysts. From the code below I am getting a NameLast of a person where their RoleId is either 3 or 4. This code will get the first occurance of that person and insert their last name into Analysts, but what I want is comma separated list of last names for all Person with RoleId 3 or 4. 

    UPDATE Dash
    SET Dash.Analysts = Person.NameLast
    FROM dbo.Dash
    INNER JOIN dbo.Item 
    ON Dash.DPItem = Item.ItemId        
    INNER JOIN dbo.Assignment 
    ON Item.ItemId = Assignment.ItemId
    INNER JOIN dbo.Person
    ON Assignment.PersonId = Person.PersonId
    WHERE (Person.RoleId = 3 or Person.RoleId = 4)

Upvotes: 0

Views: 357

Answers (1)

roman
roman

Reputation: 117550

update Dash set
    Analysts = 
    stuff(
         (
             select ', ' + P.NameLast
             from dbo.Item as I
                 inner join dbo.Assignment as A on A.ItemId = I.ItemId
                 inner join dbo.Person as P on P.PersonId = A.PersonId
             where I.ItemId = D.DPItem and P.RoleId in (3, 4)
             for xml path(''), type
         ).value('.', 'nvarchar(max)')
    , 1, 2, '')
from dbo.Dash as D

Upvotes: 1

Related Questions