Update Name with Inner join- Multiple Values

Question

I want to Make query which will set Product Name to Product NAme + Category Name where Categories can be multiple

Update P Set P.Name = ISNULL(P.Name,'')+','+ C.NAme 
from Product P
Left Outer Join Category C On P.CategoryId = C.Id.

Now one Product can have multiple categories E.g. Alpenliebe Jar product can have Multiple Categories as Toffies and Candies I want to set its Name to Alpenliebe Jar,Toffies,Candies My current query works but only setting name to Alpenliebe Jar,Toffies

Any Help

The outer join returns you data row by row & not in a single row. To update all candies with your 1st value, you must have candies data returned as a single row (comma separated). — Zo Has, Feb 03 '14 at 06:38
Right, But is there any other workaround to this, like nested subqueries or something like this — Nitin Varpe, Feb 03 '14 at 06:41
I'm not really in sql querries, but I think this may help you. -> http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Lemuel Nitor, Feb 03 '14 at 06:46
@NitinVarpe check this link http://stackoverflow.com/questions/12435284/comma-separated-values-in-one-column-sql-server There is a SQL function to return the data as csv. You can then call this function & concatenate these values with your candy name. — Zo Has, Feb 03 '14 at 06:48

score 1 · Answer 1 · edited Feb 03 '14 at 07:49

1

Try this...

UPDATE P SET P.Name = P.Name+'-'+ SUBSTRING((SELECT ', ' + C.NAme FROM Product P INNER  JOIN Category C ON P.CategoryId = C.Id 
        WHERE P.Name = PH.Name
        ORDER BY P.Name FOR XML PATH('')),2,200000) FROM Product PH

edited Feb 03 '14 at 07:49

Vignesh Kumar A

27,863
13
63
115

answered Feb 03 '14 at 07:46

sureshhh

1,196
10
15

score 1 · Answer 2 · answered Feb 03 '14 at 07:49

Try this:-

Update p
Set P.Name = STUFF((
                       SELECT P.Name +  ',' + IsNull(CName,'')
                       FROM Category  c
                       WHERE p.CategoryID = c.ID
                       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from Product p

score 0 · Answer 3 · answered Feb 03 '14 at 06:50

0

Try like this

DECLARE @Names VARCHAR(8000)  
SET @Names = SELECT COALESCE(@Names + ',', '') + C.Name FROM Category JOIN Product P On P.CategoryId = C.Id.

Update P Set P.Name = ISNULL(P.Name,'')+ ',' + @Names 
FROM Product P

COALESCE

answered Feb 03 '14 at 06:50

Vignesh Kumar A

27,863
13
63
115

But this will set same category name to all products – Nitin Varpe Feb 03 '14 at 06:54
@NitinVarpe You have join know so it will have category based on Product – Vignesh Kumar A Feb 03 '14 at 07:22

score 0 · Answer 4 · answered Feb 03 '14 at 06:59

WITH cte(Id,Name) 
AS 
(

   SELECT ID, Name
   FROM Category
   WHERE [CategoryId] IS NULL  

   UNION ALL -- starts recursion

   SELECT  ID, P.Name +',' C.Name 
   FROM Category C
   JOIN cte P ON P.ID = C.[ParentId]
)

Update P Set P.Name = C.NAme + ISNULL(P.Name,'')
from Product P
Left Outer Join Category C On P.CategoryId = C.Id

Update Name with Inner join- Multiple Values

4 Answers4

COALESCE