Des Hutch Des Hutch - 7 months ago 12
SQL Question

Combine UPDATE with INNER JOIN and STUFF Function

I am trying to use a stuff query with an UPDATE and INNER JOIN without success.
I need to stuff the results of a query into one column/row and update another table with that result.

My current code is this.

Update t1
SET t1.col1 = t2.ordernotes
FROM db.dbo.table1 t1
INNER JOIN
STUFF((
(Select order_no, Notes As ordernotes
FROM db.dbo.table2
GROUP BY order_no
ORDER BY TimeStamp
FOR XML PATH('')
),1,1,'')
As t2
ON t2.order_no = t1.order_no


The query in the inner join returns the following result

Order_no | OrderNotes
1234 | Note 1
1234 | Note 2
1234 | Note 3


The desired result is this and then updated into table 1

Order_no | OrderNotes
1234 | Note 1, Note 2, Note 3

Answer

One method uses cross apply:

Update t1
    SET t1.col1 = t2.ordernotes 
    FROM db.dbo.table1 t1 CROSS APPLY
         (SELECT STUFF((SELECT ', ' + t1.Notes
                        FROM db.dbo.table2
                        WHERE t2.order_no = t1.order_no
                        ORDER BY t2.TimeStamp
                        FOR XML PATH('')
                       ), 1, 2, ''
                      ) as ordernotes
         ) t2;

However, it is better to get in the habit of using type for string concatenation:

Update t1
    SET t1.col1 = t2.ordernotes 
    FROM db.dbo.table1 t1 CROSS APPLY
         (SELECT STUFF((SELECT ', ' + t1.Notes
                        FROM db.dbo.table2
                        WHERE t2.order_no = t1.order_no
                        ORDER BY t2.TimeStamp
                        FOR XML PATH(''), TYPE
                       ).VALUE('.', 'nvarchar(max)'
                              ), 1, 2, ''
                      ) as ordernotes
         ) t2;
Comments